﻿<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
	<title xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory">Chapter 6. EPL Reference: Named Windows and Tables</title>
	<link rel="stylesheet" href="css/espertech.css" type="text/css">
	<meta xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" name="generator" content="DocBook XSL-NS Stylesheets V1.74.0">
	<meta xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" http-equiv="Content-Type" content="text/html; charset=UTF-8">
	<link rel="home" href="index.html" title="Esper Reference">
	<link rel="up" href="index.html" title="Esper Reference">
	<link rel="prev" href="epl_clauses.html" title="Chapter 5. EPL Reference: Clauses">
	<link rel="next" href="event_patterns.html" title="Chapter 7. EPL Reference: Patterns">
</head>

<body>
	<p xmlns:d="http://docbook.org/ns/docbook" id="title"><a href="./index.html" class="site_href"><strong>www.espertech.com</strong></a><a href="http://www.espertech.com/esper/esper-documentation/" class="doc_href"><strong>Documentation</strong></a></p>
	<ul xmlns:d="http://docbook.org/ns/docbook" class="docnav">
		<li class="previous"><a accesskey="p" href="epl_clauses.html"><strong>Prev</strong></a></li>
		<li class="next"><a accesskey="n" href="event_patterns.html"><strong>Next</strong></a></li>
	</ul>
	<div class="chapter" lang="en-US">
		<div class="titlepage">
			<div>
				<div>
					<h2 class="title"><a id="nwtable"></a>Chapter 6. EPL Reference: Named Windows and Tables</h2>
				</div>
			</div>
		</div>
		<div class="toc">
			<dl>
				<dt><span class="sect1"><a href="nwtable.html#nwtable-overview">6.1. Overview</a></span></dt>
				<dd>
					<dl>
						<dt><span class="sect2"><a href="nwtable.html#nwtable-overview-namedwindow">6.1.1. Named Window Overview</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#nwtable-overview-table">6.1.2. Table Overview</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#nwtable-overview-compare">6.1.3. Comparing Named Windows and Tables</a></span></dt>
					</dl>
				</dd>
				<dt><span class="sect1"><a href="nwtable.html#nwtable-namedwindow">6.2. Named Window Usage</a></span></dt>
				<dd>
					<dl>
						<dt><span class="sect2"><a href="nwtable.html#named_create">6.2.1. Creating Named Windows: The Create Window clause</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#named_inserting">6.2.2. Inserting Into Named Windows</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#named_selecting">6.2.3. Selecting From Named Windows</a></span></dt>
					</dl>
				</dd>
				<dt><span class="sect1"><a href="nwtable.html#nwtable-table">6.3. Table Usage</a></span></dt>
				<dd>
					<dl>
						<dt><span class="sect2"><a href="nwtable.html#table_create">6.3.1. Creating Tables: The Create Table Clause</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#table_agginto">6.3.2. Aggregating Into Table Rows: The Into Table Clause</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#table_keyedaccess">6.3.3. Table Column Keyed-Access Expressions</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#nwtable-insert">6.3.4. Inserting Into Tables</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#nwtable-table-selecting">6.3.5. Selecting From Tables</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#nwtable-table-resetting">6.3.6. Resetting Table Columns and Aggregation State</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#nwtable-table-initializing">6.3.7. Initializing Table Columns and Aggregation State</a></span></dt>
					</dl>
				</dd>
				<dt><span class="sect1"><a href="nwtable.html#named_querying">6.4. Triggered Select: The On Select Clause</a></span></dt>
				<dd>
					<dl>
						<dt><span class="sect2"><a href="nwtable.html#named_querying_namedwin">6.4.1. Notes on On-Select With Named Windows</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#named_querying_table">6.4.2. Notes on On-Select With Tables</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#named_query">6.4.3. On-Select Compared to Join</a></span></dt>
					</dl>
				</dd>
				<dt><span class="sect1"><a href="nwtable.html#named_selectdelete">6.5. Triggered Select+Delete: The On Select Delete Clause</a></span></dt>
				<dt><span class="sect1"><a href="nwtable.html#named_update">6.6. Updating Data: The On Update Clause</a></span></dt>
				<dd>
					<dl>
						<dt><span class="sect2"><a href="nwtable.html#named_update_namedwin">6.6.1. Notes on On-Update With Named Windows</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#named_update_table">6.6.2. Notes on On-Update With Tables</a></span></dt>
					</dl>
				</dd>
				<dt><span class="sect1"><a href="nwtable.html#named_delete">6.7. Deleting Data: The On Delete Clause</a></span></dt>
				<dd>
					<dl>
						<dt><span class="sect2"><a href="nwtable.html#named_delete_pattern">6.7.1. Using Patterns in the On-Delete Clause</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#named_delete_namedwinsilent">6.7.2. Silent Delete With Named Windows</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#named_delete_namedwin">6.7.3. Notes on On-Delete With Named Windows</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#named_delete_table">6.7.4. Notes on On-Update With Tables</a></span></dt>
					</dl>
				</dd>
				<dt><span class="sect1"><a href="nwtable.html#named_upsert">6.8. Triggered Upsert Using the On-Merge Clause</a></span></dt>
				<dd>
					<dl>
						<dt><span class="sect2"><a href="nwtable.html#named_upsert_insertunmatched">6.8.1. On-Merge Insert</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#named_upsert_upsert">6.8.2. On-Merge Update, Delete, Select (Upsert)</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#named_merge_namedwin">6.8.3. Notes on On-Merge With Named Windows</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#named_merge_table">6.8.4. Notes on On-Merge With Tables</a></span></dt>
					</dl>
				</dd>
				<dt><span class="sect1"><a href="nwtable.html#named_explicit_index">6.9. Explicitly Indexing Named Windows and Tables</a></span></dt>
				<dt><span class="sect1"><a href="nwtable.html#nwtable-faf">6.10. Using Fire-and-Forget Queries With Named Windows and Tables</a></span></dt>
				<dd>
					<dl>
						<dt><span class="sect2"><a href="nwtable.html#named_insertfaf">6.10.1. Inserting Data</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#named_update_faf">6.10.2. Updating Data</a></span></dt>
						<dt><span class="sect2"><a href="nwtable.html#named_delete_faf">6.10.3. Deleting Data</a></span></dt>
					</dl>
				</dd>
				<dt><span class="sect1"><a href="nwtable.html#named-eventasproperty">6.11. Events as Property</a></span></dt>
			</dl>
		</div>
		<div class="sect1" lang="en-US">
			<div class="titlepage">
				<div>
					<div>
						<h2 class="title"><a id="nwtable-overview"></a>6.1. Overview</h2>
					</div>
				</div>
			</div><a id="d0e16085" class="indexterm"></a><a id="d0e16090" class="indexterm"></a>
			<p>
				A <span class="emphasis"><em>named window</em></span> is a globally-visible data window. A <span class="emphasis"><em>table</em></span> is a globally-visible data structure organized by primary key or keys.
			</p>
			<p>
				Named windows and tables both offer a way to share state between statements and are stateful.
				Named windows and tables have differing capabilities and semantics.
			</p>
			<p>
				To query a named window or table, simply use the named window name or table name in the <code class="literal">from</code> clause of your statement,
				including statements that contain subqueries, joins and outer-joins.
			</p>
			<p>
				Certain clauses operate on either a named window or a table, namely the
				<code class="literal">on-merge</code>, <code class="literal">on-update</code>, <code class="literal">on-delete</code> and <code class="literal">on-select</code> clauses.
				The fire-and-forget queries also operate on both named windows and tables.
			</p>
			<p>
				Both named windows and tables can have columns that hold events as column values, as further described in <a class="xref" href="nwtable.html#named-eventasproperty" title="6.11. Events as Property">Section 6.11, “Events as Property”</a>.
			</p>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="nwtable-overview-namedwindow"></a>6.1.1. Named Window Overview</h3>
						</div>
					</div>
				</div><a id="d0e16131" class="indexterm"></a><a id="d0e16134" class="indexterm"></a>
				<p>
					Named Windows are introduced in <a class="xref" href="processingmodel.html#processingmodel_infra_namedwindow" title="2.14.1. Named Windows">Section 2.14.1, “Named Windows”</a>.
				</p>
				<p>
					A <span class="emphasis"><em>named window</em></span> is a global data window that can take part in many statements and that can be inserted-into and deleted-from by multiple statements. A named window holds events of the same type or supertype, unless used with a variant stream.
				</p>
				<p>
					The <code class="literal">create window</code> clause declares a new named window. The named window starts up empty unless populated from an existing named window at time of deployment. Events must be inserted into the named window using the <code class="literal">insert into</code> clause. Events can also be deleted from a named window via the <code class="literal">on delete</code> clause.
				</p>
				<p>
					Events enter the named window by means of <code class="literal">insert into</code> clause of a <code class="literal">select</code> statement. Events leave a named window either because the expiry policy of the declared data window removes events from the named window, or through statements that use the <code class="literal">on delete</code> clause to explicitly delete from a named window.
				</p>
				<p>
					A named window may also decorate an event to preserve original events as described in <a class="xref" href="epl_clauses.html#insert-into-decorated" title="5.10.4. Decorated Events">Section 5.10.4, “Decorated Events”</a> and <a class="xref" href="nwtable.html#named-insertinto-decorated" title="6.2.2.1. Named Windows Holding Decorated Events">Section 6.2.2.1, “Named Windows Holding Decorated Events”</a>.
				</p>
				<p>
					To tune subquery performance when the subquery selects from a named window, consider the hints discussed in <a class="xref" href="epl_clauses.html#epl-subqueries-hints" title="5.11.8. Hints Related to Subqueries">Section 5.11.8, “Hints Related to Subqueries”</a>.
				</p>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="nwtable-overview-table"></a>6.1.2. Table Overview</h3>
						</div>
					</div>
				</div><a id="d0e16183" class="indexterm"></a><a id="d0e16186" class="indexterm"></a>
				<p>
					Tables are introduced in <a class="xref" href="processingmodel.html#processingmodel_infra_table" title="2.14.2. Tables">Section 2.14.2, “Tables”</a>.
				</p>
				<p>
					A <span class="emphasis"><em>table</em></span> is a data structure that is globally visible and that holds state.
				</p>
				<p>
					The columns of a table can store aggregation state, allowing for co-location of event data with aggregation state.
					Other statements can directly create and update the shared aggregation state. Statements can also query the aggregation state conveniently.
					Aggregation state can include comprehensive state such as for example a large matrix of long-type values for use in a Count-min sketch approximation.
					Common aggregation state can be updated by multiple statements.
				</p>
				<p>
					Use the <code class="literal">create table</code> clause to declare a new table.
				</p>
				<p>
					The atomicity guarantees under multi-threaded evaluation are as follows. For a given statement, a table row or rows either exists or do not exist, consistently,
					for the duration of the evaluation of an event or timer against a context partition of a statement. The same is true for updates in that for a given context partition of a statement,
					each table row is either completely updated or not updated at all for the duration of an evaluation. Stream-level filter expressions against tables are not part of statement evaluation
					and the same atomicity applies to stream-level filter expressions.
				</p>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="nwtable-overview-compare"></a>6.1.3. Comparing Named Windows and Tables</h3>
						</div>
					</div>
				</div>
				<p>
					As a general rule-of-thumb, if you need to share a data window between statements, the named window is the right approach.
					If however rows are organized by primary key or hold aggregation state, a table may be preferable. Statements allow the combined use of both.
				</p>
				<p>
					In summary the difference comes from named windows holding immutable events, versus tables which hold update-in-place rows and columns that can hold plain values and complex aggregation state side-by-side.
				</p>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="nwtable-overview-compare-1"></a>6.1.3.1. Nature of Data</h4>
							</div>
						</div>
					</div>
					<p>
						One important difference between named windows and tables is in the data that a row holds: While named windows hold events, tables can hold additional derived state.
					</p>
					<p>
						For example, a table column can hold rich derived state such as a distinct values set and rich aggregation state such as the state of a Count-min sketch approximation aggregation (a large matrix of long-type values).
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// Declare a table to hold a Count-min sketch approximate count per feed
create table AppoximateCountPerWord (feed string, approx countMinSketch())</pre>
				</div>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="nwtable-overview-compare-2"></a>6.1.3.2. Data Organization</h4>
							</div>
						</div>
					</div>
					<p>
						A second difference between named windows and tables is the organization of rows. For named windows, the organization of rows follows the data window declaration.
						Tables, on the other hand, can be organized by a primary key or by multiple primary keys that make up a compound key.
					</p>
					<p>
						For example, if your declaration specifies a sliding time window to hold 10 seconds of stock tick events then the rows are held in a sliding time window, i.e. a list or queue according to arrival order.
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// Declare a named window to hold 10 seconds of stock tick events
create window TenSecOfTicksWindow#time(10 sec) as StockTickEvent</pre>
					<p>
						An iterator for a named window returns rows in the order as provided by the data window(s) declared for the named window.
						An iterator for a table returns rows in an unpredictable order.
					</p>
				</div>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="nwtable-overview-compare-3"></a>6.1.3.3. Insert and Remove Stream</h4>
							</div>
						</div>
					</div>
					<p>
						Only named windows provide an insert and remove stream to other statements. Tables do not provide an insert and remove stream.
					</p>
					<p>
						For example, considering the <code class="literal">TenSecOfTicksWindow</code> named window declared above,
						the following statement outputs the current count each time events enter or leave the named window.
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">select count(*) from TenSecOfTicksWindow</pre>
					<p>
						Also for example, considering the <code class="literal">AppoximateCountPerWord</code> table declared above, the following EPL does not output any rows
						when table rows gets inserted, updated or deleted and only outputs rows when the statement is iterated:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// does not continously output for table changes
select * from AppoximateCountPerWord</pre>
				</div>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="nwtable-overview-compare-4"></a>6.1.3.4. Immutability and Copy-On-Write</h4>
							</div>
						</div>
					</div>
					<p>
						As named windows hold events and events are immutable, when an update statement updates events held in a named window, the runtime performs a logical copy operation (copy-on-write, as configured for the type) of each updated event, and only modifies the newly created event, preserving the immutable original event.
					</p>
					<p>
						Data in tables are updated in-place. There is no copy operation for table rows.
					</p>
				</div>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="nwtable-overview-compare-5"></a>6.1.3.5. Removal of Rows</h4>
							</div>
						</div>
					</div>
					<p>
						For named windows, the data window declared for the named window instructs the runtime to expire and remove events from the named window.
						Events can also be removed via <code class="literal">on-merge</code>, <code class="literal">on-delete</code> and fire-and-forget <code class="literal">delete</code>.
					</p>
					<p>
						For tables, row can only be removed via <code class="literal">on-merge</code>, <code class="literal">on-delete</code>, <code class="literal">on-select-and-delete</code> and fire-and-forget <code class="literal">delete</code>.
					</p>
				</div>
			</div>
		</div>
		<div class="sect1" lang="en-US">
			<div class="titlepage">
				<div>
					<div>
						<h2 class="title"><a id="nwtable-namedwindow"></a>6.2. Named Window Usage</h2>
					</div>
				</div>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_create"></a>6.2.1. Creating Named Windows: The <code class="literal">Create Window</code> clause</h3>
						</div>
					</div>
				</div>
				<p>
					The <code class="literal">create window</code> statement creates a named window by specifying a window name and one or more data windows, as well as the type of event to hold in the named window.
				</p>
				<p>
					There are two syntaxes for creating a named window: The first syntax allows modeling a named window after an existing event type or an existing named window. The second syntax is similar to the SQL create-table syntax and provides a list of column names and column types.
				</p>
				<p>
					A new named window starts up empty. It must be explicitly inserted into by one or more statements, as discussed below. A named window can also be populated at time of creation from an existing named window.
				</p>
				<p>
					Your application can only undeploy the statement that created the named window after all other statements that use the named window are also undeployed.
				</p>
				<p>
					The <code class="literal">create window</code> statement posts to listeners any events that are inserted into the named window as new data. The statement posts all deleted events or events that expire out of the data window to listeners as the remove stream (old data). The named window contents can also be iterated on via the pull API to obtain the current contents of a named window.
				</p>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="named_create_modelafter"></a>6.2.1.1. Creation by Modeling After an Existing Type</h4>
							</div>
						</div>
					</div>
					<p>
						The benefit of modeling a named window after an existing event type is that event properties can be nested, indexed, mapped or other types that your event objects may provide as properties, including the type of the underlying event itself. Also, using the wildcard (*) operator means your EPL does not need to list each individual property explicitly.
					</p>
					<p>
						The syntax for creating a named window by modeling the named window after an existing event type, is as follows:
					</p>
					<pre class="synopsis">[context <span class="emphasis"><em>context_name</em></span>] 
		create window <span class="emphasis"><em>window_name</em></span>.<span class="emphasis"><em>window_spec</em></span> 
		[as] [select <span class="emphasis"><em>list_of_properties</em></span> from] <span class="emphasis"><em>event_type_or_windowname</em></span>
		[insert [where <span class="emphasis"><em>filter_expression</em></span>]]</pre>
					<p>
						The <span class="emphasis"><em>window_name</em></span> you assign to the named window can be any identifier. The name should not already be in use as an event type or stream name or table name.
					</p>
					<p>
						The <span class="emphasis"><em>window_spec</em></span> are one or more data windows that define the expiry policy for removing events from the named window. Named windows must explicitly declare a data window. This is required to ensure that the policy for retaining events in the data window is well defined. To keep all events, use the keep-all window: It indicates that the named window should keep all events and only remove events from the named window that are deleted by <code class="literal">on delete</code>, <code class="literal">on merge</code> or fire-and-forget <code class="literal">delete</code>. Data windows are listed in <a class="xref" href="epl-views.html" title="Chapter 14. EPL Reference: Data Windows">Chapter 14, <i>EPL Reference: Data Windows</i></a>.
					</p>
					<p>
						The <code class="literal">select</code> clause and <span class="emphasis"><em>list_of_properties</em></span> are optional. If present, they specify the column names and, implicitly by definition of the event type, the column types of events held by the named window. Expressions other than column names are not allowed in the <code class="literal">select</code> list of properties. Wildcards (*) and wildcards with additional properties can also be used.
					</p>
					<p>
						The <span class="emphasis"><em>event_type_or_windowname</em></span> is required if using the model-after syntax. It provides the name of the event type of events held in the data window, unless column names and types have been explicitly selected via <code class="literal">select</code>. The name of an (existing) other named window is also allowed here. Please find more details in <a class="xref" href="nwtable.html#named_populate_named" title="6.2.1.4. Populating a Named Window From an Existing Named Window">Section 6.2.1.4, “Populating a Named Window From an Existing Named Window”</a>.
					</p>
					<p>
						Finally, the <code class="literal">insert</code> clause and optional <span class="emphasis"><em>filter_expression</em></span> are used if the new named window is modelled after an existing named window, and the data of the new named window is to be populated from the existing named window upon deployment. The optional <span class="emphasis"><em>filter_expression</em></span> can be used to exclude events.
					</p>
					<p>
						You may refer to a context by specifying the <code class="literal">context</code> keyword followed by a context name. Contexts are described in more detail at <a class="xref" href="context.html" title="Chapter 4. Context and Context Partitions">Chapter 4, <i>Context and Context Partitions</i></a>. The effect of referring to a context is that your named window operates according to the context dimensional information as declared for the context. For usage and limitations please see the respective chapter.
					</p>
					<p>
						The next statement creates a named window <code class="literal">OrdersNamedWindow</code> for which the expiry policy is simply to keep all events. Assume that the event type 'OrderMapEventType' has been configured. The named window is to hold events of type 'OrderMapEventType':
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create window OrdersNamedWindow#keepall as OrderMapEventType</pre>
					<p>
						The below sample statement demonstrates the <code class="literal">select</code> syntax. It defines a named window in which each row has the three properties 'symbol', 'volume' and 'price'. This named window actively removes events from the window that are older than 30 seconds.
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create window OrdersTimeWindow#time(30 sec) as 
		select symbol, volume, price from OrderEvent</pre>
					<p>
						In an alternate form, the <code class="literal">as</code> keyword can be used to rename columns, and constants may occur in the select-clause as well:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create window OrdersTimeWindow#time(30 sec) as 
  select symbol as sym, volume as vol, price, 1 as alertId from OrderEvent</pre>
					<div xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="note">
						<h2>Note</h2>
						<p>
							The <code class="literal">select</code>-clause when used with <code class="literal">create window</code> provides types information only. You must still use <code class="literal">on-merge</code> or <code class="literal">insert-into</code> to insert events.
						</p>
					</div>
				</div>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="named_create_explicit"></a>6.2.1.2. Creation By Defining Columns Names and Types</h4>
							</div>
						</div>
					</div>
					<p>
						The second syntax for creating a named window is by supplying column names and types:
					</p>
					<pre class="synopsis">[context <span class="emphasis"><em>context_name</em></span>] 
create window <span class="emphasis"><em>window_name</em></span>.<span class="emphasis"><em>window_spec</em></span> [as] (<span class="emphasis"><em>column_name</em></span> <span class="emphasis"><em>column_type</em></span> 
  [,<span class="emphasis"><em>column_name</em></span> <span class="emphasis"><em>column_type</em></span> [,...])</pre>
					<p>
						The <span class="emphasis"><em>column_name</em></span> is an identifier providing the event property name. The <span class="emphasis"><em>column_type</em></span> is also required for each column. Valid column types are listed in <a class="xref" href="epl_clauses.html#variable_create" title="5.17.1. Creating Variables: The Create Variable Clause">Section 5.17.1, “Creating Variables: The Create Variable Clause”</a>
						and are the same as for variable types.
					</p>
					<p>
						For attributes that are array-type append <code class="literal">[]</code> (left and right brackets).
					</p>
					<p>
						The next statement creates a named window:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create window SecurityEvent#time(30 sec) 
(ipAddress string, userId String, numAttempts int, properties String[])</pre>
					<p>
						Named window columns can hold events by declaring the column type as the event type name. Array-type in combination with event-type is also supported.
					</p>
					<p>
						The next two statements declare an event type and create a named window with a column of the defined event type:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create schema SecurityData (name String, roles String[])</pre>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create window SecurityEvent#time(30 sec) 
    (ipAddress string, userId String, secData SecurityData, historySecData SecurityData[])</pre>
					<p>
						Whether the named window uses a Map, Object-array, JSON or Avro event representation for the rows can be specified as follows. If the create-window statement provides the <code class="literal">@EventRepresentation(objectarray)</code> annotation the runtime maintains named window rows as object array, for example.If no annotation is provided, the runtime uses the configured default event representation as discussed in <a class="xref" href="configuration.html#configuration-common-eventmeta-representation" title="17.4.9.1. Default Event Representation">Section 17.4.9.1, “Default Event Representation”</a>.
					</p>
					<p>
						The following statement instructs the runtime to represent FooWindow rows as object arrays:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">@EventRepresentation(objectarray) create window FooWindow#time(5 sec) as (string prop1)</pre>
				</div>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="named_create_drop"></a>6.2.1.3. Dropping or Removing Named Windows</h4>
							</div>
						</div>
					</div>
					<p>
						There is no syntax to drop or remove a named window.
					</p>
					<p>
						Undeploying the deployment that created the named window also removes the named window.
					</p>
				</div>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="named_populate_named"></a>6.2.1.4. Populating a Named Window From an Existing Named Window</h4>
							</div>
						</div>
					</div><a id="d0e16515" class="indexterm"></a><a id="d0e16518" class="indexterm"></a><a id="d0e16523" class="indexterm"></a>
					<p>
						Your statement may specify the name of an existing named window when creating a new named window, and may use the <code class="literal">insert</code> keyword to indicate that the new named window is to be populated from
						the events currently held by the existing named window.
					</p>
					<p>
						For example, and assuming the named window <code class="literal">OrdersNamedWindow</code> already exists, this statement creates a new named window <code class="literal">ScratchOrders</code> and populates all orders in <code class="literal">OrdersNamedWindow</code> into the new named window:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create window ScratchOrders#keepall as OrdersNamedWindow insert</pre>
					<p>
						The <code class="literal">where</code> keyword is also available to perform filtering, for example:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create window ScratchBuyOrders#time(10) as OrdersNamedWindow insert where side = 'buy'</pre>
				</div>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_inserting"></a>6.2.2. Inserting Into Named Windows</h3>
						</div>
					</div>
				</div><a id="d0e16558" class="indexterm"></a><a id="d0e16563" class="indexterm"></a>
				<p>
					The <code class="literal">insert into</code> clause inserts events into named windows. Your application must ensure that the column names and types match the declared column names and types of the named window to be inserted into.
				</p>
				<p>
					For inserting into a named window and for simultaneously checking if the inserted row already exists in the named window or for atomic update-insert operation on a named window, or for targeting a specific partition, you must use <code class="literal">on-merge</code> as described in <a class="xref" href="nwtable.html#named_upsert" title="6.8. Triggered Upsert Using the On-Merge Clause">Section 6.8, “Triggered Upsert Using the On-Merge Clause”</a>. On-merge
					is similar to the SQL <code class="literal">merge</code> clause and provides what is known as an "Upsert" operation: Update existing events or if no existing event(s) are found then insert a new event, all in one atomic operation provided by a single statement.
				</p>
				<p>
					This example first creates a named window using some of the columns of an OrderEvent event type:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create window OrdersWindow#keepall as select symbol, volume, price from OrderEvent</pre>
				<p>
					The insert into the named window selects individual columns to be inserted:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">insert into OrdersWindow(symbol, volume, price) select name, count, price from FXOrderEvent</pre>
				<p>
					An alternative form is shown next:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">insert into OrdersWindow select name as symbol, vol as volume, price from FXOrderEvent</pre>
				<p>
					Following above statement, the runtime enters every FXOrderEvent arriving into the runtime into the named window 'OrdersWindow'.
				</p>
				<p>
					The following statements create a named window for an event type backed by a Java class and insert into the window any 'OrderEvent' where the symbol value is IBM:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create window OrdersWindow#time(30) as com.mycompany.OrderEvent</pre>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">insert into OrdersWindow select * from com.mycompany.OrderEvent(symbol='IBM')</pre>
				<p>
					The last example adds one column named 'derivedPrice' to the 'OrderEvent' type by specifying a wildcard, and uses a user-defined function to populate the column:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create window OrdersWindow#time(30) as select *, price as derivedPrice from OrderEvent</pre>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">insert into OrdersWindow select *, MyFunc.func(price, percent) as derivedPrice from OrderEvent</pre>
				<p>
					Event representations based on Java base classes or interfaces, and subclasses or implementing classes, are compatible as these statements show:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// create a named window for the base class
create window OrdersWindow#unique(name) as select * from ProductBaseEvent</pre>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// The ServiceProductEvent class subclasses the ProductBaseEvent class
insert into OrdersWindow select * from ServiceProductEvent</pre>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// The MerchandiseProductEvent class subclasses the ProductBaseEvent class
insert into OrdersWindow select * from MerchandiseProductEvent</pre>
				<p>
					To avoid duplicate events inserted in a named window and atomically check if a row already exists, use <code class="literal">on-merge</code> as outlined in <a class="xref" href="nwtable.html#named_upsert" title="6.8. Triggered Upsert Using the On-Merge Clause">Section 6.8, “Triggered Upsert Using the On-Merge Clause”</a>. An example:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on ServiceProductEvent as spe merge OrdersWindow as win
where win.id = spe.id when not matched then insert select *</pre>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="named-insertinto-decorated"></a>6.2.2.1. Named Windows Holding Decorated Events</h4>
							</div>
						</div>
					</div>
					<p>
						Decorated events hold an underlying event and add additional properties to the underlying event, as described further in <a class="xref" href="epl_clauses.html#insert-into-decorated" title="5.10.4. Decorated Events">Section 5.10.4, “Decorated Events”</a>.
					</p>
					<p>
						This sample statement creates a named window that decorates OrderEvent events by adding an additional property named <code class="literal">priceTotal</code> to each OrderEvent. A matching <code class="literal">insert into</code> statement is also part of the sample:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create window OrdersWindow#time(30) as select *, price as priceTotal from OrderEvent</pre>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">insert into OrdersWindow select *, price * unit as priceTotal from ServiceOrderEvent</pre>
					<p>
						The property type of the additional <code class="literal">priceTotal</code> column is the property type of the existing <code class="literal">price</code> property of OrderEvent.
					</p>
				</div>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_selecting"></a>6.2.3. Selecting From Named Windows</h3>
						</div>
					</div>
				</div><a id="d0e16658" class="indexterm"></a><a id="d0e16663" class="indexterm"></a>
				<p>
					A named window can be referred to by any statement in the <code class="literal">from</code> clause of the statement. Filter criteria can also be specified. Data windows cannot be specified when selecting from a named window (the special derived-value windows are allowed however).
				</p>
				<p>
					A statement selecting all events from a named window <code class="literal">OrdersNamedWindow</code> is shown next. The named window must first be created via the <code class="literal">create window</code> clause before use.
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">select * from OrdersNamedWindow</pre>
				<p>
					The statement as above simply receives the unfiltered insert stream of the named window and reports that stream to its listeners. The <code class="literal">iterator</code> method returns all events in the named window, if any.
				</p>
				<p>
					If your application desires to obtain the events removed from the named window, use the <code class="literal">rstream</code> keyword as this statement shows:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">select rstream * from OrdersNamedWindow</pre>
				<p>
					The next statement derives an average price per symbol for the events held by the named window:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">select symbol, avg(price) from OrdersNamedWindow group by symbol</pre>
				<p>
					A statement that consumes from a named window, like the one above,
					receives the insert and remove stream of the named window. The insert stream represents the events inserted into the named window. The remove stream represents the events expired from
					the named window data window and the events explicitly deleted via <code class="literal">on-delete</code> for fire-and-forget (on-demand) <code class="literal">delete</code>.
				</p>
				<p>
					Your application may create a consuming statement such as above on an empty named window, or your application may create the above statement on an already filled named window. The runtime provides correct results in either case:
					At the time of deployment the runtime internally initializes the consuming statement from the current named window, also taking your declared filters into consideration. Thus, your statement deriving data from a named window does not start empty if the named window already holds one or more events. A consuming statement also sees the remove stream of an already populated named window, if any.
				</p>
				<p>
					If you require a subset of the data in the named window, you can specify one or more filter expressions onto the named window as shown here:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">select symbol, avg(price) from OrdersNamedWindow(sector='energy') group by symbol</pre>
				<p>
					By adding a filter to the named window, the aggregation and grouping receive a filtered insert and remove stream. The above statement thus outputs, continuously, the average price per symbol for all orders in the named window that belong to a certain sector.
				</p>
				<p>
					A side note on variables in filters filtering events from named windows: The runtime initializes consuming statements at statement deployment time and changes aggregation state continuously as events arrive. If the filter criteria contain variables and variable values changes, then the runtime does not re-evaluate or re-build aggregation state. In such a case you may want to place variables in the <code class="literal">having</code> clause which evaluates on already-built aggregation state.
				</p>
				<p>
					The following example further declares a custom derived-value window named <code class="literal">myplugindatawindow</code>.
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">select * from OrdersNamedWindow(volume&gt;0, price&gt;0)#myplugindatawindow()</pre>
				<p>
					Data windows cannot be used onto named windows since named windows post insert and remove streams for the events entering and leaving the named window, thus the expiry policy and batch behavior are well defined by the data window declared for the named window. For example, the following is not allowed and fails at time of statement compilation:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// not a valid statement
select * from OrdersNamedWindow#time(30 sec)</pre>
			</div>
		</div>
		<div class="sect1" lang="en-US">
			<div class="titlepage">
				<div>
					<div>
						<h2 class="title"><a id="nwtable-table"></a>6.3. Table Usage</h2>
					</div>
				</div>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="table_create"></a>6.3.1. Creating Tables: The <code class="literal">Create Table</code> Clause</h3>
						</div>
					</div>
				</div>
				<p>
					The <code class="literal">create table</code> statement creates a table.
				</p>
				<p>
					A new table starts up empty. It must be explicitly aggregated-into using <code class="literal">into table</code>, or populated by an <code class="literal">on-merge</code> statement, or populated by <code class="literal">insert into</code>.
				</p>
				<p>
					The syntax for creating a table provides the table name, lists column names and types and designates primary key columns:
				</p>
				<pre class="synopsis">[context <span class="emphasis"><em>context_name</em></span>] 
create table <span class="emphasis"><em>table_name</em></span> [as] (<span class="emphasis"><em>column_name</em></span> <span class="emphasis"><em>column_type</em></span> [<code class="literal">primary key</code>]
  [,<span class="emphasis"><em>column_name</em></span> <span class="emphasis"><em>column_type</em></span> [<code class="literal">primary key</code>] [,...]])</pre>
				<p>
					The <span class="emphasis"><em>table_name</em></span> you assign to the table can be any identifier. The name should not already be in use as an event type or named window name.
				</p>
				<p>
					You may refer to a context by specifying the <code class="literal">context</code> keyword followed by a context name. Contexts are described in more detail at <a class="xref" href="context.html" title="Chapter 4. Context and Context Partitions">Chapter 4, <i>Context and Context Partitions</i></a>. The effect of referring to a context is that your table operates according to the context dimensional information as declared for the context. For usage and limitations please see the respective chapter.
				</p>
				<p>
					The <span class="emphasis"><em>column_name</em></span> is an identifier providing the column name.
				</p>
				<p>
					The <span class="emphasis"><em>column_type</em></span> is required for each column. There are two categories of column types:
				</p>
				<div xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="orderedlist">
					<ol>
						<li>
							<p>
								Non-aggregating column types: Valid column types are listed in <a class="xref" href="epl_clauses.html#variable_create" title="5.17.1. Creating Variables: The Create Variable Clause">Section 5.17.1, “Creating Variables: The Create Variable Clause”</a> and are the same as for variable types.
								For attributes that are array-type append <code class="literal">[]</code> (left and right brackets).
								Table columns can hold events by declaring the column type as the event type name. Array-type in combination with event-type is also supported.
							</p>
						</li>
						<li>
							<p>
								Aggregation column types: These instruct the runtime to retain aggregation state.
							</p>
						</li>
					</ol>
				</div>
				<p>
					After each column type you may add the <code class="literal">primary key</code> keywords. This keyword designates the column as a primary key.
					When multiple columns are designated as primary key columns the combination of column values builds a compound primary key.
					The order in which the primary key columns are listed is important.
					Further detail on key expressions can be found at <a class="xref" href="epl_clauses.html#epl-syntax-compositekey" title="5.2.13. Composite Keys and Array Values as Keys">Section 5.2.13, “Composite Keys and Array Values as Keys”</a>.
				</p>
				<p>
					The next statement creates a table to hold a <code class="literal">numAttempts</code> count aggregation state and a column named <code class="literal">active</code> of type boolean, per <code class="literal">ipAddress</code> and <code class="literal">userId</code>:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create table SecuritySummaryTable (
  ipAddress string primary key,
  userId String primary key, 
  numAttempts count(*),
  active boolean)</pre>
				<p>
					The example above specifies <code class="literal">ipAddress</code> and <code class="literal">userId</code> as primary keys. This instructs the runtime that the table holds a single row
					for each distinct combination of <code class="literal">ipAddress</code> and <code class="literal">userId</code>. The two values make up the compound key and there is a single row per compound key value.
				</p>
				<p>
					If you do not designate any columns of the table as a primary key column, the table holds only one row (or no rows).
				</p>
				<p>
					The create table statement does not provide output to its listeners. The table contents can be iterated on via the pull API to obtain the current contents of a table.
				</p>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="nwtable-table-aggtypessql"></a>6.3.1.1. Column Types for Aggregation Functions</h4>
							</div>
						</div>
					</div>
					<p>
						All aggregation functions can be used as column types for tables. Please simply list the aggregation function name as the column type
						and provide type information, when required.
						See <a class="xref" href="functionreference.html#epl-function-aggregation-std" title="10.2.1. SQL-Standard Functions">Section 10.2.1, “SQL-Standard Functions”</a> for a list of the functions and required parameter expressions for which you must provide type information.
					</p>
					<p>
						Consider the next example that declares a table with columns for different aggregation functions (not a comprehensive example of all possible aggregation functions):
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create table MyStats (
  myKey string primary key,
  myAvedev avedev(int), // column holds a mean deviation of int-typed values
  myAvg avg(double), // column holds an average of double-typed values
  myCount count(*), // column holds a count
  myMax max(int), // column holds a highest int-typed value
  myMedian median(float), // column holds the median of float-typed values
  myStddev stddev(java.math.BigDecimal), // column holds a standard deviation of BigDecimal values
  mySum sum(long), // column holds a sum of long values
  myFirstEver firstever(string), // column holds a first-ever value of type string
  myCountEver countever(*) // column holds the count-ever (regardless of data windows)
)</pre>
					<p>
						Additional keywords such as <code class="literal">distinct</code> can be used as well. If your aggregation will be associated with a filter expression, you must add <code class="literal">boolean</code> to
						the parameters in the column type declaration.
					</p>
					<p>
						For example, the next EPL declares a table with aggregation-type columns that hold an average of filtered double-typed values and an average of distinct double-typed values:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create table MyStatsMore (
  myKey string primary key,
  myAvgFiltered avg(double, boolean), // column holds an average of double-typed values
                      // and filtered by a boolean expression to be provided
  myAvgDistinct avg(distinct double) // column holds an average of distinct double-typed values
)</pre>
				</div>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="nwtable-table-aggtypesother"></a>6.3.1.2. Column Types for Event Aggregation Functions</h4>
							</div>
						</div>
					</div>
					<p>
						The event aggregation functions can be used as column types for tables.
						For event aggregation functions you must specify the event type using the <code class="literal">@type(</code><span class="emphasis"><em>name</em></span><code class="literal">)</code> annotation.
					</p>
					<p>
						The <code class="literal">window</code> event aggregation function requires the <code class="literal">*</code> wildcard.
						The <code class="literal">first</code> and <code class="literal">last</code> cannot be used in a declaration, please use <code class="literal">window</code> instead and access
						as described in <a class="xref" href="nwtable.html#table_keyedaccess_aggregation" title="6.3.3.2. Using Aggregation Methods to Access Aggregation State with the Dot Operator">Section 6.3.3.2, “Using Aggregation Methods to Access Aggregation State with the Dot Operator”</a>.
					</p>
					<p>
						The <code class="literal">sorted</code>, <code class="literal">maxbyever</code> and <code class="literal">minbyever</code> event aggregation functions require the criteria expression as a parameter.
						The criteria expression must only use properties of the provided event type.
						The <code class="literal">maxby</code> and <code class="literal">minby</code> cannot be used in a declaration, please use <code class="literal">sorted</code> instead and access
						as described in <a class="xref" href="nwtable.html#table_keyedaccess_aggregation" title="6.3.3.2. Using Aggregation Methods to Access Aggregation State with the Dot Operator">Section 6.3.3.2, “Using Aggregation Methods to Access Aggregation State with the Dot Operator”</a>.
					</p>
					<p>
						In this example the table declares sample event aggregations (not a comprehensive example of all possible aggregations):
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create table MyEventAggregationTable (
  myKey string primary key,
  myWindow window(*) @type(MyEvent), // column holds a window of MyEvent events
  mySorted sorted(mySortValue) @type(MyEvent), // column holds MyEvent events sorted by mySortValue
  myMaxByEver maxbyever(mySortValue) @type(MyEvent) // column holds the single MyEvent event that 
        // provided the highest value of mySortValue ever
)</pre>
				</div>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="nwtable-table-aggtypesplugin"></a>6.3.1.3. Column Types for Plug-In Custom Aggregation Functions</h4>
							</div>
						</div>
					</div>
					<p>
						Any custom single-function and multi-function aggregation can be used as a table column type. If the aggregation has multiple different return values and aggregations share common state, the multi-function aggregation is the preferred API.
					</p>
					<p>
						For example, the next EPL declares a table with a single column that holds the state of the aggregation function <code class="literal">myAggregation</code>:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create table MyStatsCustom (myCustom myAggregation('some code', 100))</pre>
					<p>
						The above example passes the values <code class="literal">some code</code> and <code class="literal">100</code> to show how to pass constants to your custom aggregation function at declaration time.
					</p>
				</div>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="nwtable-table-drop"></a>6.3.1.4. Dropping or Removing Tables</h4>
							</div>
						</div>
					</div>
					<p>
						There is no syntax to drop or remove a table.
					</p>
					<p>
						Undeploying the deployment that created the table also removes the table.
					</p>
					<p>
						The <code class="literal">stop</code> method on the <code class="literal">EPStatement</code> that created the table has no effect.
					</p>
				</div>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="table_agginto"></a>6.3.2. Aggregating Into Table Rows: The <code class="literal">Into Table</code> Clause</h3>
						</div>
					</div>
				</div>
				<p>
					Use the <code class="literal">into table</code> keywords to instruct the runtime to aggregate into table columns. A given statement can only aggregate into a single table.
				</p>
				<p>
					For example, consider a table that holds the count of intrusion events keyed by the combination of from-address and to-address:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create table IntrusionCountTable (
  fromAddress string primary key,
  toAddress string primary key,
  countIntrusion10Sec count(*),
  countIntrusion60Sec count(*)
)</pre>
				<p>
					The next sample statement updates the count considering the last 10 seconds of events:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">into table IntrusionCountTable
select count(*) as countIntrusion10Sec
from IntrusionEvent#time(10)
group by fromAddress, toAddress</pre>
				<p>
					For statements that have a data window, like the example above, the <code class="literal">into table</code> instructs the runtime to update the aggregations that reside in the table column for events entering the data window and for events expiring from the data window.
					For statements that don't have a data window the <code class="literal">into table</code> instructs the runtime to update the aggregation for arriving events.
				</p>
				<p>
					In the example above, when an <code class="literal">IntrusionEvent</code> enters the 10-second time window the runtime increments the count aggregation by one.
					When an <code class="literal">IntrusionEvent</code> gets removed from (expires from) the 10-second time window the runtime decrements the count aggregation by one.
				</p>
				<p>
					Multiple statements can aggregate into the same table columns or different table columns.
					The co-aggregating ability allows you to co-locate aggregation state conveniently.
				</p>
				<p>
					The sample shown below is very similar to the previous statement except that it updates the count considering the last 60 seconds of events:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">into table IntrusionCountTable
select count(*) as countIntrusion60Sec
from IntrusionEvent#time(60)
group by fromAddress, toAddress</pre>
				<p>
					Considering the example above, when an intrusion event arrives and a row for the group-by key values (from and to-address) does not exists,
					the runtime creates a new row and updates the aggregation-type columns.
					If the row for the group-by key values exists, the runtime updates the aggregation-type columns of the existing row.
				</p>
				<p>
					Tables can have no primary key columns. In this case a table either has a single row or is empty.
				</p>
				<p>
					The next two statements demonstrate table use without a primary key column:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create table TotalIntrusionCountTable (totalIntrusions count(*))</pre>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">into table TotalIntrusionCountTable select count(*) as totalIntrusions from IntrusionEvent</pre>
				<p>
					In conjunction with <code class="literal">into table</code> the <code class="literal">unidirectional</code> keyword is not supported.
				</p>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="table_agginto_requiregroupby"></a>6.3.2.1. Group-By Clause Requirements</h4>
							</div>
						</div>
					</div>
					<p>
						The use of the <code class="literal">into table</code> clause requires that the <code class="literal">group by</code> clause must list group-by expressions
						that match the table's primary key declarations in terms of the number, return type and order of group-by expressions.
						It is not necessary that table column names match group-by expression texts.
					</p>
					<p>
						For example consider a table with a single long-type primary key column:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create table MyTable (theKey long primary key, theCount count(*))</pre>
					<p>
						The following EPL are all not valid:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// Invalid: No group-by clause however the table declares a primary key
into table MyTable select count(*) as theCount from MyEvent</pre>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// Invalid: Two expressions in the group-by clause however the table declares a single primary key
into table MyTable select count(*) as theCount from MyEvent group by longPropertyOne, longPropertyTwo</pre>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// Invalid: The group-by clause expression returns a string-typed value however the table expects a long-type primary key
into table MyTable select count(*) as theCount from MyEvent group by stringProperty</pre>
					<p>
						You may use the <code class="literal">rollup</code>, <code class="literal">cube</code> and <code class="literal">grouping sets</code> keywords in conjunction with tables.
					</p>
				</div>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="table_agginto_requireaggcomplete"></a>6.3.2.2. Aggregation State Requirements</h4>
							</div>
						</div>
					</div>
					<p>
						The use of the <code class="literal">into table</code> clause requires that all aggregation state of the statement resides in table columns.
					</p>
					<p>
						For example consider a simple table as follows:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create table MyTable (theKey long primary key, theCount count(*))</pre>
					<p>
						The following EPL is not valid:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// Invalid: the sum aggregation state is not available in a table column
into table MyTable select count(*) as theCount, sum(intProperty) from MyEvent group by longProperty</pre>
				</div>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="table_agginto_requireaggfunc"></a>6.3.2.3. Aggregation Function Requirements</h4>
							</div>
						</div>
					</div>
					<p>
						The use of the <code class="literal">into table</code> clause requires that all aggregation functions that are listed in the statement are compatible with table column types,
						and that the statement has at least one aggregation function.
					</p>
					<p>
						For example consider a simple table as follows:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create table MyTable (theKey long primary key, theCount count(*))</pre>
					<p>
						The following EPL is not valid:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// Invalid: the sum aggregation state is not compatible with count(*) that was declared for the table column's type
into table MyTable select sum(intProperty) as theCount from MyEvent group by longProperty</pre>
					<p>
						If declared, the <code class="literal">distinct</code> keyword and filter expressions must also match. The event type information must match for event aggregation functions.
					</p>
				</div>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="table_agginto_requirecolname"></a>6.3.2.4. Column Naming Requirements</h4>
							</div>
						</div>
					</div>
					<p>
						The use of the <code class="literal">into table</code> clause requires that the aggregation functions are named.
						You can name an expression two ways.
					</p>
					<div xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="orderedlist">
						<ol>
							<li>
								<p>
									First, you can name the aggregation function expression by adding it to the select-clause and by providing the <code class="literal">as</code>-keyword
									followed by the table column name. The examples earlier use this technique.
								</p>
							</li>
							<li>
								<p>
									Second, you can name the aggregation function by placing it into a declared expression that carries the same name as the table column.
								</p>
							</li>
						</ol>
					</div>
					<p>
						This example demonstrates the second method of naming an aggregation function:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">expression alias totalIntrusions {count(*)}
select totalIntrusions from IntrusionEvent</pre>
				</div>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="table_keyedaccess"></a>6.3.3. Table Column Keyed-Access Expressions</h3>
						</div>
					</div>
				</div>
				<p>
					For accessing table columns by primary key, EPL provides a convenient syntax that allows you to read table column values simply by providing the table name, primary key value expressions (if required by the table) and the column name.
				</p>
				<p>
					The synopsis for table-column access expressions is:
				</p>
				<pre class="synopsis"><span class="emphasis"><em>table-name</em></span>[<span class="emphasis"><em>primary_key_expr</em></span> [, <span class="emphasis"><em>primary_key_expr</em></span>] [,...]][.<span class="emphasis"><em>column-name]</em></span></pre>
				<p>
					The expression starts with the table name.
					If the table declares primary keys you must provide the <span class="emphasis"><em>primary_key_expr</em></span> value expressions for each primary key within square brackets.
					To access a specific column, add the (<code class="literal">.</code>) dot character and the column name.
				</p>
				<p>
					For example, consider a table that holds the count of intrusion events keyed by the combination of from-address and to-address:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create table IntrusionCountTable (
  fromAddress string primary key,
  toAddress string primary key,
  countIntrusion10Sec count(*)
)</pre>
				<p>
					Assuming that a <code class="literal">FireWallEvent</code> has string-type properties named <code class="literal">from</code> and <code class="literal">to</code>, the next
					statement outputs the current 10-second intrusion count as held by the <code class="literal">IntrusionCountTable</code> row for the matching combination of keys:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">select IntrusionCountTable[from, to].countIntrusion10Sec from FirewallEvent</pre>
				<p>
					The number of primary key expressions, the return type of the primary key expressions and the order in which they are provided must match
					the primary key columns that were declared for the table.
					If the table does not have any primary keys declared, you cannot provide any primary key expressions.
				</p>
				<p>
					If a row for the primary key (or compound key) cannot be found, the runtime returns a <code class="literal">null</code> value.
				</p>
				<p>
					An example table without primary key columns is shown next:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create table TotalIntrusionCountTable (totalIntrusions count(*))</pre>
				<p>
					A sample statement that outputs the current total count every 60 seconds is:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">select TotalIntrusionCountTable.totalIntrusions from pattern[every timer:interval(60 sec)]</pre>
				<p>
					Table access expressions can be used anywhere in statements except as parameter expressions for data windows, the <code class="literal">update istream</code>,
					context declarations, output limit expressions, pattern observer and guard parameters, pattern every-distinct, pattern match-until bounds, pattern followed-by max
					and <code class="literal">create window</code> insert or select expression and as a <code class="literal">create variable</code> assignment expression.
				</p>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="table_keyedaccess_allcolumns"></a>6.3.3.1. Reading All Column Values</h4>
							</div>
						</div>
					</div>
					<p>
						If your keyed-access expression emits the column name, the runtime returns all current column values.
					</p>
					<p>
						An example EPL:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">select IntrusionCountTable[from, to] from FirewallEvent</pre>
					<p>
						The runtime returns each column value, or null if no row is found. For aggregation-type columns it returns the current aggregation value.
					</p>
				</div>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="table_keyedaccess_aggregation"></a>6.3.3.2. Using Aggregation Methods to Access Aggregation State with the Dot Operator</h4>
							</div>
						</div>
					</div>
					<p>
						Certain aggregation functions allow accessing aggregation state using the (<code class="literal">.</code>) dot operator.
						The methods that operate on aggregation state are called aggregation methods.
						Aggregation methods are explained further in <a class="xref" href="aggmethodreference.html" title="Chapter 13. EPL Reference: Aggregation Methods">Chapter 13, <i>EPL Reference: Aggregation Methods</i></a>.
					</p>
					<p>
						The EPL shown below declares a table that keeps an unsorted set of events and a sorted set of events. This sample table has no primary key columns:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create table MyTable (
  theWindow window(*) @type(MyEvent),
  theSorted sorted(mySortValue) @type(MyEvent)
)</pre>
					<p>
						This statement uses the <code class="literal">first</code> and the <code class="literal">maxBy</code> aggregation methods to return the first (oldest) event and the greatest event according to the sort value:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">select MyTable.theWindow.first(), MyTable.theSorted.maxBy() from SomeOtherEvent</pre>
				</div>
				<div class="sect3" lang="en-US">
					<div class="titlepage">
						<div>
							<div>
								<h4 class="title"><a id="table_keys_obtaining"></a>6.3.3.3. Obtaining All Table Keys</h4>
							</div>
						</div>
					</div>
					<p>
						Tables provide the <code class="literal">keys</code> method that returns all table keys. The synopsis is:
					</p>
					<pre class="synopsis"><span class="emphasis"><em>tablename</em></span>.keys()</pre>
					<p>
						The return type of the method is object-array. Each element of the array is a table key. For composite table keys, each element of the array is itself of type object-array and each array element is the primary key value.
					</p>
					<p>
						An example EPL:
					</p>
					<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">select IntrusionCountTable.keys() from FirewallEvent</pre>
					<p>
						The runtime returns an array of primary keys.
					</p>
				</div>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="nwtable-insert"></a>6.3.4. Inserting Into Tables</h3>
						</div>
					</div>
				</div><a id="d0e17260" class="indexterm"></a><a id="d0e17265" class="indexterm"></a>
				<p>
					The <code class="literal">insert into</code> clause inserts rows into a table.
					Your application must ensure that the column names and types match the declared column names and types of the table to be inserted into, when provided.
				</p>
				<p>
					For inserting into a table and for simultaneously checking if the inserted row already exists in the table or for atomic update-insert operation on a table, or for targeting a specific partition, you must use
					<code class="literal">on-merge</code> as described in <a class="xref" href="nwtable.html#named_upsert" title="6.8. Triggered Upsert Using the On-Merge Clause">Section 6.8, “Triggered Upsert Using the On-Merge Clause”</a>. On-merge
					is similar to the SQL <code class="literal">merge</code> clause and provides what is known as an "Upsert" operation: Update existing rows or if no existing rows(s) are found then insert a new row,
					all in one atomic operation provided by a single statement.
				</p>
				<p>
					The following statement populates the example table declared earlier:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">insert into IntrusionCountTable select fromAddress, toAddress from FirewallEvent</pre>
				<p>
					Note that when a row with the same primary key values already exists, your statement may encounter a unique index violation at runtime.
					If the inserted-into table does not have primary key columns, the table holds a maximum of one row and your statement may also encounter a unique index violation upon
					attempting to insert a second row.
					Use <code class="literal">on-merge</code> to prevent inserts of duplicate rows.
				</p>
				<p>
					Table columns that are aggregation functions cannot be inserted-into and must be updated using <code class="literal">into table</code> instead.
				</p>
				<p>
					You may also explicitly list column names as discussed earlier in <a class="xref" href="nwtable.html#named_inserting" title="6.2.2. Inserting Into Named Windows">Section 6.2.2, “Inserting Into Named Windows”</a>.
					For <code class="literal">insert-into</code>, the context name must be the same context name as declared for the <code class="literal">create table</code> statement or the context name must be absent for both.
				</p>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="nwtable-table-selecting"></a>6.3.5. Selecting From Tables</h3>
						</div>
					</div>
				</div><a id="d0e17314" class="indexterm"></a><a id="d0e17319" class="indexterm"></a>
				<p>
					A table can be referred to by any statement in the <code class="literal">from</code>-clause of the statement.
				</p>
				<p>
					Tables do not provide an insert and remove stream. When a table appears alone in the <code class="literal">from</code>-clause (other than as part of a subquery),
					the statement produces output only when iterated (see pull API) or when executing a fire-and-forget (on-demand) query.
				</p>
				<p>
					Assuming you have declared a table by name <code class="literal">IntrusionCountTable</code> as shown earlier,
					the following statement only returns rows when iterated or when executing the EPL as a fire-and-forget (on-demand) query or when adding an <code class="literal">output snapshot</code>:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">select * from IntrusionCountTable</pre>
				<p>
					For tables, the contained-event syntax and specifying a data window is not supported.
					In a join, a table in the <code class="literal">from</code>-clause cannot be marked as <code class="literal">unidirectional</code>. You may not specify any of the retain-flags.
					Tables cannot be used in the <code class="literal">from</code>-clause of match-recognize statements, in context declarations, in pattern filter atoms and <code class="literal">update istream</code>.
				</p>
				<p>
					The following are examples of invalid statements:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// invalid statement examples
select * from IntrusionCountTable#time(30 sec)   // data window not allowed
select * from IntrusionCountTable unidirectional, MyEvent   // tables cannot be marked as unidirectional</pre>
				<p>
					Tables can be used in subqueries and joins.
				</p>
				<p>
					It follows a sample subselect and join against the table:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">select
  (select * from IntrusionCountTable as intr
   where intr.fromAddress = firewall.fromAddress and intr.toAddress = firewall.toAddress) 
from IntrusionEvent as firewall</pre>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">select * from IntrusionCountTable as intr, IntrusionEvent as firewall
where intr.fromAddress = firewall.fromAddress and intr.toAddress = firewall.toAddress</pre>
				<p>
					If the subselect or join specifies all of a table's primary key columns, please consider using the table-access expression instead. It offers a more concise syntax.
				</p>
				<p>
					Note that for a subquery against a table that may return multiple rows, the information about subquery multi-row selection applies. For subselects, consider using <code class="literal">@eventbean</code>
					to preserve table type information in the output event.
				</p>
				<p>
					Note that for joins against tables the compiler does not allow specifying table filter expressions in parenthesis, in the <code class="literal">from</code> clause.
					Filter expressions must instead be placed into the <code class="literal">where</code>-clause.
				</p>
				<p>
					You may access aggregation state the same way as in table-access expressions, using the dot (<code class="literal">.</code>) operator.
				</p>
				<p>
					The EPL shown below declares a table that keeps a set of events, and shows a join that selects window aggregation state:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create table MyWindowTable (theWindow window(*) @type(MyEvent))</pre>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">select theWindow.first(), theWindow.last(), theWindow.window() from MyEvent, MyWindowTable</pre>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="nwtable-table-resetting"></a>6.3.6. Resetting Table Columns and Aggregation State</h3>
						</div>
					</div>
				</div><a id="d0e17401" class="indexterm"></a><a id="d0e17406" class="indexterm"></a><a id="d0e17413" class="indexterm"></a><a id="d0e17418" class="indexterm"></a>
				<p>
					For resetting plain table columns, you may simple assign a value using <code class="literal">set</code> <span class="emphasis"><em>name</em></span> <code class="literal">=</code> <span class="emphasis"><em>value</em></span> within an <code class="literal">on-merge update-</code> action to set new values.
					You may also use fire-and-forget queries.
				</p>
				<p>
					For resetting aggregation-typed table columns, as they represent aggregation state, it is not possible to use a straight assignment. Instead, the runtime provides a <code class="literal">reset</code> method.
					The syntax for the reset method is <span class="emphasis"><em>column-name</em></span><code class="literal">.reset()</code> and is only available as part of <code class="literal">on-merge</code> and the <code class="literal">update</code>-action.
					You may also reset all aggregations of a table row by specifying <span class="emphasis"><em>table-alias</em></span><code class="literal">.reset()</code>.
				</p>
				<p>
					For example, consider a table that holds the count of intrusion events keyed by the combination of from-address and to-address:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create table IntrusionCountTable (
  fromAddress string primary key,
  toAddress string primary key,
  countIntrusion10Sec count(*),
  countIntrusion60Sec count(*)
)</pre>
				<p>
					Assume there is an event that triggers the reset of the <code class="literal">countIntrusion10Sec</code> and <code class="literal">countIntrusion60Sec</code> counts:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create schema IntrusionReset(fromAddress string, toAddress string)</pre>
				<p>
					Use <code class="literal">on-merge</code> to reset the count columns like so:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on IntrusionReset as resetEvent merge IntrusionCountTable as tableRow
where resetEvent.fromAddress = tableRow.fromAddress and resetEvent.toAddress = tableRow.toAddress
when matched then update set countIntrusion10Sec.reset(), countIntrusion60Sec.reset()</pre>
				<p>
					Alternatively you may specify the table alias to reset all of a table row's aggregation state:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on IntrusionReset as resetEvent merge IntrusionCountTable as tableRow
where resetEvent.fromAddress = tableRow.fromAddress and resetEvent.toAddress = tableRow.toAddress
when matched then update set tableRow.reset()</pre>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="nwtable-table-initializing"></a>6.3.7. Initializing Table Columns and Aggregation State</h3>
						</div>
					</div>
				</div><a id="d0e17491" class="indexterm"></a><a id="d0e17496" class="indexterm"></a><a id="d0e17503" class="indexterm"></a><a id="d0e17508" class="indexterm"></a>
				<p>
					For initializing plain table columns, you may simple assign a value using <code class="literal">set</code> <span class="emphasis"><em>name</em></span> <code class="literal">=</code> <span class="emphasis"><em>value</em></span> within an <code class="literal">on-merge update-</code> action to set new values.
					You may also use fire-and-forget queries.
				</p>
				<p>
					For initializing aggregation-typed table columns, as they represent aggregation state, it is not possible to use a straight assignment. Instead, you must use <code class="literal">into table</code> since aggregation state is complex state and requires adding and removing values or events and therefore cannot be assigned to.
				</p>
				<p>
					If the aggregation-typed column is a count, please declare the column as a sum instead and use <code class="literal">sum(initial value)</code> to initialize the value or <code class="literal">sum(1)</code> to increment the sum.
				</p>
			</div>
		</div>
		<div class="sect1" lang="en-US">
			<div class="titlepage">
				<div>
					<div>
						<h2 class="title"><a id="named_querying"></a>6.4. Triggered Select: The <code class="literal">On Select</code> Clause</h2>
					</div>
				</div>
			</div><a id="d0e17551" class="indexterm"></a><a id="d0e17554" class="indexterm"></a><a id="d0e17559" class="indexterm"></a>
			<p>
				The <code class="literal">on select</code> clause performs a one-time, non-continuous query on a named window or table every time a triggering event arrives or a triggering pattern matches. The statement can consider all rows, or only rows that match certain criteria, or rows that correlate with an arriving event or a pattern of arriving events.
			</p>
			<p>
				The syntax for the <code class="literal">on select</code> clause is as follows:
			</p>
			<pre class="synopsis">on <span class="emphasis"><em>event_type</em></span>[<span class="emphasis"><em>(filter_criteria)</em></span>] [as <span class="emphasis"><em>stream_name</em></span>]
[insert into <span class="emphasis"><em>insert_into_def</em></span>]
select <span class="emphasis"><em>select_list</em></span>
from <span class="emphasis"><em>window_or_table_name</em></span> [as <span class="emphasis"><em>stream_name</em></span>]
[where <span class="emphasis"><em>criteria_expression</em></span>]
[group by <span class="emphasis"><em>grouping_expression_list</em></span>]
[having <span class="emphasis"><em>grouping_search_conditions</em></span>]
[order by <span class="emphasis"><em>order_by_expression_list</em></span>]
</pre>
			<p>
				The <span class="emphasis"><em>event_type</em></span> is the name of the type of events that trigger the query against the named window or table. It is optionally followed by <span class="emphasis"><em>filter_criteria</em></span> which are filter expressions to apply to arriving events. The optional <code class="literal">as</code> keyword can be used to assign a stream name. Patterns or named windows can also be specified in the <code class="literal">on</code> clause, see the samples in <a class="xref" href="nwtable.html#named_delete_pattern" title="6.7.1. Using Patterns in the On-Delete Clause">Section 6.7.1, “Using Patterns in the On-Delete Clause”</a> (for a named window as a trigger only insert stream events trigger actions) (tables cannot be triggers).
			</p>
			<p>
				The <span class="emphasis"><em>insert into</em></span> clause works as described in <a class="xref" href="epl_clauses.html#epl-insert-into" title="5.10. Merging Streams and Continuous Insertion: The Insert Into Clause">Section 5.10, “Merging Streams and Continuous Insertion: The Insert Into Clause”</a>. The <span class="emphasis"><em>select</em></span> clause is described in <a class="xref" href="epl_clauses.html#epl-select-list" title="5.3. Choosing Event Properties and Events: The Select Clause">Section 5.3, “Choosing Event Properties and Events: The Select Clause”</a>. For all clauses the semantics are equivalent to a join operation: The properties of the triggering event or events are available in the <code class="literal">select</code> clause and all other clauses.
			</p>
			<p>
				The <span class="emphasis"><em>window_or_table_name</em></span> in the <code class="literal">from</code> clause is the name of the named window or table to select rows from. The <code class="literal">as</code> keyword is also available to assign a stream name to the table or named window. The <code class="literal">as</code> keyword is helpful in conjunction with wildcard in the <code class="literal">select</code> clause to select rows via the syntax <code class="literal">select streamname.* </code>.
			</p>
			<p>
				The optional <code class="literal">where</code> clause contains a <span class="emphasis"><em>criteria_expression</em></span> that correlates the arriving (triggering) event to the rows to be considered from the table or named window. The <span class="emphasis"><em>criteria_expression</em></span> may also simply filter for rows to be considered by the statement.
			</p>
			<p>
				The <code class="literal">group by</code> clause, the <code class="literal">having</code> clause and the <code class="literal">order by</code> clause are all optional and work as described in earlier chapters.
			</p>
			<p>
				Statements that use tables and named windows work the same. The examples herein use the <code class="literal">OrdersNamedWindow</code> named window and the <code class="literal">SecuritySummaryTable</code> table to provide examples for each.
			</p>
			<p>
				The sample statement below outputs, when a trigger event arrives, the count of all rows held by the <code class="literal">SecuritySummaryTable</code> table:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on QueryEvent select count(*) from SecuritySummaryTable</pre>
			<p>
				This sample statement outputs the total volume per symbol ordered by symbol ascending and only non-zero volumes of all rows held by the <code class="literal">OrdersNamedWindow</code> named window:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on QueryEvent
select symbol, sum(volume) from OrdersNamedWindow
group by symbol having volume &gt; 0 order by symbol</pre>
			<p>
				When using wildcard (*) to select from streams in an on-select clause, each stream, that is the triggering stream and the selected-upon table or named window, are selected, similar to a join.
				Therefore your wildcard select returns two columns: the triggering event and the selection result row, for each row.
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on QueryEvent as queryEvent
select * from OrdersNamedWindow as win</pre>
			<p>
				The statement above returns a <code class="literal">queryEvent</code> column and a <code class="literal">win</code> column for each event.
				If only a single stream's event is desired in the result, use <code class="literal">select win.*</code> instead.
			</p>
			<p>
				Upon arrival of a QueryEvent event, this statement selects all rows in the <code class="literal">OrdersNamedWindow</code> named window:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on QueryEvent select win.* from OrdersNamedWindow as win</pre>
			<p>
				The runtime executes the statement on arrival of a triggering event, in this case a QueryEvent. It posts the statement results to any listeners to the statement, in a single invocation, as the new data array.
			</p>
			<p>
				The <code class="literal">where</code> clause filters and correlates rows in the table or named window with the triggering event, as shown next:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on QueryEvent(volume&gt;0) as query
select query.symbol, query.volume, win.symbol  from OrdersNamedWindow as win
where win.symbol = query.symbol</pre>
			<p>
				Upon arrival of a QueryEvent, if that event has a value for the volume property that is greater than zero, the runtime executes the statement. The statement considers all events currently held by the <code class="literal">OrdersNamedWindow</code> that match the symbol property value of the triggering QueryEvent event.
			</p>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_querying_namedwin"></a>6.4.1. Notes on On-Select With Named Windows</h3>
						</div>
					</div>
				</div>
				<p>
					For correlated statements that correlate triggering events with rows held by a named window, the runtime internally creates efficient indexes to enable high performance querying of rows.
					It analyzes the <code class="literal">where</code> clause to build one or more indexes for fast lookup in the named window based on the properties of the triggering event.
				</p>
				<p>
					To trigger an on-select when an update to the selected named window occurs or when the triggering event is the same event that is being inserted into the named window,
					specify the named window name as the event type.
				</p>
				<p>
					The next statement fires the select for every change to the named window OrdersNamedWindow:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on OrdersNamedWindow as trig 
select onw.symbol, sum(onw.volume) 
from OrdersNamedWindow as onw 
where onw.symbol = trig.symbol</pre>
				<p>
					For named windows, the <code class="literal">iterator</code> of the <code class="literal">EPStatement</code> object representing the <code class="literal">on select</code> clause returns the last batch of selected events in response to the last triggering event, or null if the last triggering event did not select any rows.
				</p>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_querying_table"></a>6.4.2. Notes on On-Select With Tables</h3>
						</div>
					</div>
				</div>
				<p>
					For tables, the <code class="literal">iterator</code> of the <code class="literal">EPStatement</code> object representing the <code class="literal">on select</code> clause returns no events.
				</p>
				<p>
					For correlated statements that correlate triggering events with rows held by a table, the runtime utilizes either primary key columns or
					secondary explicitly-created indexes to enable high performance querying of rows, based on an analysis of the <code class="literal">where</code> clause.
				</p>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_query"></a>6.4.3. <code class="literal">On-Select</code> Compared to Join</h3>
						</div>
					</div>
				</div>
				<p>
					The similarities and differences between an <code class="literal">on select</code> clause and a regular or outer join (and not unidirectional) are as follows:
				</p>
				<div xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="orderedlist">
					<ol>
						<li>
							<p>
								A join is evaluated when any of the streams participating in the join have new events (insert stream) or events leaving data windows (remove stream). A join is therefore bi-directional or multi-directional. However, the <code class="literal">on select</code> statement has one triggering event or pattern that causes the statement to be evaluated and is thus uni-directional.
							</p>
						</li>
						<li>
							<p>
								The query within the <code class="literal">on select</code> statement is not continuous: It executes only when a triggering event or pattern occurs. Aggregation and groups are computed anew considering the contents of the table or named window at the time the triggering event arrives.
							</p>
						</li>
					</ol>
				</div>
				<p>
					<code class="literal">On-select</code> and the unidirectional join can be compared as follows.
				</p>
				<p>
					<code class="literal">On-select</code>, <code class="literal">on-merge</code>, <code class="literal">on-insert</code>, <code class="literal">on-delete</code>, <code class="literal">on-update</code> and <code class="literal">on-select-and-delete</code>
					operate only on named windows or tables. Unidirectional joins however can operate on any stream.
					If the unidirectional join is between a single named window or table and a triggering event or pattern and that triggering event or pattern is marked unidirectional, the unidirectional join is equivalent to <code class="literal">on-select</code>.
				</p>
				<p>
					A unidirectional join does not execute under a named window context partition lock and instead is a consumer relationship to the named window.
				</p>
			</div>
		</div>
		<div class="sect1" lang="en-US">
			<div class="titlepage">
				<div>
					<div>
						<h2 class="title"><a id="named_selectdelete"></a>6.5. Triggered Select+Delete: The <code class="literal">On Select Delete</code> Clause</h2>
					</div>
				</div>
			</div><a id="d0e17845" class="indexterm"></a><a id="d0e17848" class="indexterm"></a><a id="d0e17853" class="indexterm"></a>
			<p>
				The <code class="literal">on select delete</code> clause performs a one-time, non-continuous query on a table or named window every time a triggering event arrives or a triggering pattern matches, similar to <code class="literal">on-select</code> as described in the previous section. In addition, any selected rows are also deleted.
			</p>
			<p>
				The syntax for the <code class="literal">on select delete</code> clause is as follows:
			</p>
			<pre class="synopsis">on <span class="emphasis"><em>trigger</em></span>
select [and] delete <span class="emphasis"><em>select_list</em></span>...
... (please see on-select for <code class="literal">insert into, from, group by, having, order by</code>)...</pre>
			<p>
				The syntax follows the syntax of <code class="literal">on-select</code> as described earlier. The <code class="literal">select</code> clause follows the optional <code class="literal">and</code> keyword and the <code class="literal">delete</code> keyword. The <code class="literal">from</code>-clause can list either a table or a named window.
			</p>
			<p>
				The example statement below selects and deletes all rows from <code class="literal">OrdersNamedWindow</code> named window when a QueryEvent arrives:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on QueryEvent select and delete window(win.*) as rows from OrdersNamedWindow as win</pre>
			<p>
				The sample EPL above also shows the use of the <code class="literal">window</code> aggregation function. It specifies the <code class="literal">window</code> aggregation function
				to instruct the runtime to output a single event, regardless of the number of rows in the named window, and that contains a column <code class="literal">rows</code> that contains a collection of the selected event's underlying objects.
			</p>
			<div xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="note">
				<h2>Note</h2>
				<p>
					<code class="literal">On Select Delete</code> deletes all rows that match the where-clause. When there is no where-clause it deletes all rows regardless of what the output looks like. The <code class="literal">having</code>-clause is relevant to output only and does not narrow down the rows that are deleted.
				</p>
			</div>
		</div>
		<div class="sect1" lang="en-US">
			<div class="titlepage">
				<div>
					<div>
						<h2 class="title"><a id="named_update"></a>6.6. Updating Data: The <code class="literal">On Update</code> Clause</h2>
					</div>
				</div>
			</div><a id="d0e17934" class="indexterm"></a><a id="d0e17937" class="indexterm"></a><a id="d0e17942" class="indexterm"></a>
			<p>
				An <code class="literal">on update</code> clause updates rows held by a table or named window. The clause can be used to update all rows, or only rows that match certain criteria, or rows that correlate with an arriving event or a pattern of arriving events.
			</p>
			<p>
				For updating a table or named window and for simultaneously checking if the updated row exists or for atomic update-insert operation on a named window or table, consider using <code class="literal">on-merge</code> as described in <a class="xref" href="nwtable.html#named_upsert" title="6.8. Triggered Upsert Using the On-Merge Clause">Section 6.8, “Triggered Upsert Using the On-Merge Clause”</a>.
				On-merge is similar to the SQL <code class="literal">merge</code> clause and provides what is known as an "Upsert" operation:
				Update existing events or if no existing event(s) are found then insert a new event, all in one atomic operation provided by a single statement.
			</p>
			<p>
				The syntax for the <code class="literal">on update</code> clause is as follows:
			</p>
			<pre class="synopsis">on <span class="emphasis"><em>event_type</em></span>[<span class="emphasis"><em>(filter_criteria)</em></span>] [as <span class="emphasis"><em>stream_name</em></span>]
update <span class="emphasis"><em>window_or_table_name</em></span> [as <span class="emphasis"><em>stream_name</em></span>]
set <span class="emphasis"><em>mutation_expression</em></span> [, <span class="emphasis"><em>mutation_expression</em></span> [,...]]
[where <span class="emphasis"><em>criteria_expression</em></span>]</pre>
			<p>
				The <span class="emphasis"><em>event_type</em></span> is the name of the type of events that trigger an update of rows in a named window. It is optionally followed by <span class="emphasis"><em>filter_criteria</em></span> which are filter expressions to apply to arriving events. The optional <code class="literal">as</code> keyword can be used to assign a name for use in expressions and the <code class="literal">where</code> clause. Patterns and named windows can also be specified in the <code class="literal">on</code> clause.
			</p>
			<p>
				The <span class="emphasis"><em>window_or_table_name</em></span> is the name of the table or named window to update rows.
				The <code class="literal">as</code> keyword is also available to assign a name to the named window or table.
			</p>
			<p>
				After the <code class="literal">set</code> keyword follows a list of comma-separated <span class="emphasis"><em>mutation_expression</em></span> expressions. A mutation expression is any valid EPL expression and including the below.
				Subqueries may by part of expressions however aggregation functions and the <code class="literal">prev</code> or <code class="literal">prior</code> function may not be used in expressions.
			</p>
			<p>
				The below table shows some typical mutation expessions:
			</p>
			<div class="table"><a id="d0e18036"></a>
				<p class="title"><b>Table 6.1. Mutation Expressions in Update and Merge</b></p>
				<div class="table-contents">
					<table summary="Mutation Expressions in Update and Merge" border="1">
						<colgroup>
							<col>
							<col>
						</colgroup>
						<thead>
							<tr>
								<th>Description</th>
								<th>Syntax and Examples</th>
							</tr>
						</thead>
						<tbody>
							<tr>
								<td>Assignment</td>
								<td>
									<pre class="synopsis"><span class="emphasis"><em>property_name</em></span> = <span class="emphasis"><em>value_expression</em></span></pre>
									<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">price = 10, side = 'BUY'</pre>
								</td>
							</tr>
							<tr>
								<td>Array Element Assignment</td>
								<td>
									<pre class="synopsis"><span class="emphasis"><em>arrayproperty_name</em></span>[<span class="emphasis"><em>array_index_expression</em></span>] = <span class="emphasis"><em>value_expression</em></span></pre>
									<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">values[position] = 10</pre>
								</td>
							</tr>
							<tr>
								<td>Event Method Invocation (not available for tables)</td>
								<td>
									<pre class="synopsis"><span class="emphasis"><em>alias_or_windowname</em></span>.<span class="emphasis"><em>methodname</em></span>(...)</pre>
									<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">orderWindow.clear()</pre>
								</td>
							</tr>
							<tr>
								<td>Property Method Invocation</td>
								<td>
									<pre class="synopsis"><span class="emphasis"><em>property_name</em></span>.<span class="emphasis"><em>methodname</em></span>(...)</pre>
									<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">accountMap.clear()</pre>
								</td>
							</tr>
							<tr>
								<td>Any Expression, For Example: User-Defined Function Call</td>
								<td>
									<pre class="synopsis"><span class="emphasis"><em>functionname</em></span>(...)</pre>
									<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">clearQuantities(orderRow)</pre>
								</td>
							</tr>
						</tbody>
					</table>
				</div>
			</div><br class="table-break">
			<p>
				The optional <code class="literal">where</code> clause contains a <span class="emphasis"><em>criteria_expression</em></span> that correlates the arriving (triggering) event to the rows to be updated in the table or named window. The <span class="emphasis"><em>criteria_expression</em></span> may also simply filter for rows to be updated.
			</p>
			<p>
				Statements that use tables and named windows work the same. We use the term property and column interchangeably. The examples herein use the <code class="literal">OrdersNamedWindow</code> named window and the <code class="literal">SecuritySummaryTable</code> table to provide examples for each. Let's look at a couple of examples.
			</p>
			<p>
				In the simplest form, this statement updates all rows in the named window <code class="literal">OrdersNamedWindow</code> when any <code class="literal">UpdateOrderEvent</code> event arrives, setting the price property to zero for all rows currently held by the named window:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on UpdateOrderEvent update OrdersNamedWindow set price = 0</pre>
			<p>
				This example demonstrates the use of a <code class="literal">where</code> clause and updates the <code class="literal">SecuritySummaryTable</code> table.
				Upon arrival of a triggering <code class="literal">ResetEvent</code> it updates the <code class="literal">active</code> column value to false for all table rows that have an <code class="literal">active</code> column value of true:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on ResetEvent update SecuritySummaryTable set active = false where active = true</pre>
			<p>
				The next example shows a more complete use of the syntax, and correlates the triggering event with rows held by the <code class="literal">OrdersNamedWindow</code> named window:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on NewOrderEvent(volume&gt;0) as myNewOrders
update OrdersNamedWindow as myNamedWindow 
set price = myNewOrders.price
where myNamedWindow.symbol = myNewOrders.symbol</pre>
			<p>
				In the above sample statement, only if a <code class="literal">NewOrderEvent</code> event with a volume greater then zero arrives does the statement trigger. Upon triggering, all rows in the named window that have the same value for the symbol property as the triggering <code class="literal">NewOrderEvent</code> event are then updated (their price property is set to that of the arriving event). The statement also showcases the <code class="literal">as</code> keyword to assign a name for use in the <code class="literal">where</code> expression.
			</p>
			<p>
				Your application can subscribe a listener to your <code class="literal">on update</code> statements to determine update events. The statement post any rows that are updated to all listeners attached to the statement as new data, and the events prior to the update as old data.
			</p>
			<p>
				The following example shows the use of tags and a pattern. It sets the price value of orders to that of either a <code class="literal">FlushOrderEvent</code> or <code class="literal">OrderUpdateEvent</code> depending on which arrived:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on pattern [every ord=OrderUpdateEvent(volume&gt;0) or every flush=FlushOrderEvent] 
update OrdersNamedWindow as win
set price = case when ord.price is null then flush.price else ord.price end
where ord.id = win.id or flush.id = win.id</pre>
			<p>
				When updating indexed properties use the syntax <span class="emphasis"><em>propertyName</em></span><code class="literal">[</code><span class="emphasis"><em>index</em></span><code class="literal">] = </code><span class="emphasis"><em>value</em></span> with the index value being an integer number.
				When updating mapped properties use the syntax <span class="emphasis"><em>propertyName</em></span><code class="literal">(</code><span class="emphasis"><em>key</em></span><code class="literal">) = </code><span class="emphasis"><em>value</em></span> with the key being a string value.
			</p>
			<p>
				The runtime executes assignments in the order they are listed. When performing multiple assignments, the runtime takes the most recent column value according to the last assignment, if any. To instruct the runtime to use the initial value before update, prefix the column name with the literal <code class="literal">initial</code>. The <code class="literal">initial</code> prefix is only available for use with named windows and not for use with tables.
			</p>
			<p>
				The following statement illustrates:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on UpdateEvent as upd
update MyWindow as win
set field_a = 1, 
  field_b = win.field_a, // assigns the value 1 
  field_c = initial.field_a // assigns the field_a original value before update</pre>
			<p>
				The next example assumes that your application provides a user-defined function <code class="literal">copyFields</code> that receives 3 parameters:
				The update event, the new row and the initial state before-update row.
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on UpdateEvent as upd update MyWindow as win set copyFields(win, upd, initial)</pre>
			<p>
				You may invoke a method on a value object, for those properties that hold value objects, as follows:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on UpdateEvent update MyWindow as win set someproperty.clear()</pre>
			<p>
				For named windows only, you may also invoke a method on the named window event type.
			</p>
			<p>
				The following example assumes that your event type provides a method by name <code class="literal">populateFrom</code> that receives the update event as a parameter:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on UpdateEvent as upd update MyWindow as win set win.populateFrom(upd)</pre>
			<p>
				The following restrictions apply:
			</p>
			<div xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="orderedlist">
				<ol>
					<li>
						<p>Each property to be updated via assignment must be writable. For tables, all columns are always writable.</p>
					</li>
					<li>
						<p>For underlying event representations that are Java objects, a event object class must implement the java.io.Serializable interface as discussed in <a class="xref" href="epl_clauses.html#epl-update-object" title="5.20.1. Immutability and Updates">Section 5.20.1, “Immutability and Updates”</a> and must provide setter methods for updated properties.</p>
					</li>
					<li>
						<p>When using an XML underlying event type, event properties in the XML document representation are not available for update.</p>
					</li>
					<li>
						<p>Nested properties are not supported for update. Variant streams may also not be updated.</p>
					</li>
				</ol>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_update_namedwin"></a>6.6.1. Notes on On-Update With Named Windows</h3>
						</div>
					</div>
				</div>
				<p>
					Statements that reference the named window receive the new event in the insert stream and the event prior to the update in the remove stream.
				</p>
				<p>
					For correlated statements (as above) that correlate triggering events with events held by a named window, the runtime internally creates efficient indexes to enable high performance update of events.
				</p>
				<p>
					The <code class="literal">iterator</code> of the <code class="literal">EPStatement</code> object representing the <code class="literal">on update</code> clause does not return any rows.
				</p>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_update_table"></a>6.6.2. Notes on On-Update With Tables</h3>
						</div>
					</div>
				</div>
				<p>
					On-Update may not update primary key columns.
				</p>
				<p>
					For correlated statements that correlate triggering events with rows held by a table, the runtime utilizes either primary key columns or
					secondary explicitly-created indexes to enable high performance querying of rows, based on an analysis of the <code class="literal">where</code> clause.
				</p>
				<p>
					The <code class="literal">iterator</code> of the <code class="literal">EPStatement</code> object representing the <code class="literal">on update</code> clause does not return any rows.
				</p>
			</div>
		</div>
		<div class="sect1" lang="en-US">
			<div class="titlepage">
				<div>
					<div>
						<h2 class="title"><a id="named_delete"></a>6.7. Deleting Data: The <code class="literal">On Delete</code> Clause</h2>
					</div>
				</div>
			</div><a id="d0e18329" class="indexterm"></a><a id="d0e18332" class="indexterm"></a><a id="d0e18337" class="indexterm"></a>
			<p>
				An <code class="literal">on delete</code> clause removes rows from a named window or table. The clause can be used to remove all rows, or only rows that match certain criteria, or rows that correlate with an arriving event or a pattern of arriving events.
			</p>
			<p>
				The syntax for the <code class="literal">on delete</code> clause is as follows:
			</p>
			<pre class="synopsis">on <span class="emphasis"><em>event_type</em></span>[<span class="emphasis"><em>(filter_criteria)</em></span>] [as <span class="emphasis"><em>stream_name</em></span>]
delete from <span class="emphasis"><em>window_or_table_name</em></span> [as <span class="emphasis"><em>stream_name</em></span>]
[where <span class="emphasis"><em>criteria_expression</em></span>]</pre>
			<p>
				The <span class="emphasis"><em>event_type</em></span> is the name of the type of events that trigger removal from the table or named window. It is optionally followed by <span class="emphasis"><em>filter_criteria</em></span> which are filter expressions to apply to arriving events. The optional <code class="literal">as</code> keyword can be used to assign a name for use in the <code class="literal">where</code> clause. Patterns and named windows can also be specified in the <code class="literal">on</code> clause as described in the next section.
			</p>
			<p>
				The <span class="emphasis"><em>window_or_table_name</em></span> is the name of the named window or table to delete rows from.
				The <code class="literal">as</code> keyword is also available to assign a name to the table or named window.
			</p>
			<p>
				The optional <code class="literal">where</code> clause contains a <span class="emphasis"><em>criteria_expression</em></span> that correlates the arriving (triggering) event to the rows to be removed. The <span class="emphasis"><em>criteria_expression</em></span> may also simply filter for rows without correlating.
			</p>
			<p>
				On-delete can be used against tables and named windows. The examples herein use the <code class="literal">OrdersNamedWindow</code> named window and the <code class="literal">SecuritySummaryTable</code> table to provide examples for each.
			</p>
			<p>
				In the simplest form, this statement deletes all rows from the <code class="literal">SecuritySummaryTable</code> table when any <code class="literal">ClearEvent</code> arrives:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on ClearEvent delete from SecuritySummaryTable</pre>
			<p>
				The next example shows a more complete use of the syntax, and correlates the triggering event with events held by the <code class="literal">OrdersNamedWindow</code> named window:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on NewOrderEvent(volume&gt;0) as myNewOrders
delete from OrdersNamedWindow as myNamedWindow 
where myNamedWindow.symbol = myNewOrders.symbol</pre>
			<p>
				In the above sample statement, only if a <code class="literal">NewOrderEvent</code> event with a volume greater then zero arrives does the statement trigger. Upon triggering, all rows in the named window that have the same value for the symbol property as the triggering <code class="literal">NewOrderEvent</code> event are removed. The statement also showcases the <code class="literal">as</code> keyword to assign a name for use in the <code class="literal">where</code> expression.
			</p>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_delete_pattern"></a>6.7.1. Using Patterns in the <code class="literal">On-Delete</code> Clause</h3>
						</div>
					</div>
				</div>
				<p>
					By means of patterns the <code class="literal">on delete</code> clause and <code class="literal">on select</code> clause (described below) can look for more complex conditions to occur, possibly involving multiple events or the passing of time. The syntax for <code class="literal">on delete</code> with a pattern expression is show next:
				</p>
				<pre class="synopsis">on pattern [<span class="emphasis"><em>pattern_expression</em></span>] [as <span class="emphasis"><em>stream_name</em></span>]
delete from <span class="emphasis"><em>window_or_table_name</em></span> [as <span class="emphasis"><em>stream_name</em></span>]
[where <span class="emphasis"><em>criteria_expression</em></span>]</pre>
				<p>
					The <span class="emphasis"><em>pattern_expression</em></span> is any pattern that matches zero or more arriving events. Tags can be used to name events in the pattern and can occur in the optional <code class="literal">where</code> clause to correlate to events to be removed from a named window.
				</p>
				<p>
					In the next example the triggering pattern fires every 10 seconds. The effect is that every 10 seconds the statement removes all rows from the <code class="literal">SecuritySummaryTable</code> table:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on pattern [every timer:interval(10 sec)] delete from SecuritySummaryTable</pre>
				<p>
					The following example shows the use of tags in a pattern and executes against the <code class="literal">OrdersNamedWindow</code> named window instead:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on pattern [every ord=OrderEvent(volume&gt;0) or every flush=FlushOrderEvent] 
delete from OrdersNamedWindow as win
where ord.id = win.id or flush.id = win.id</pre>
				<p>
					The pattern above looks for OrderEvent events with a volume value greater then zero and tags such events as 'ord'.
					The pattern also looks for FlushOrderEvent events and tags such events as 'flush'. The <code class="literal">where</code> clause deletes from the <code class="literal">OrdersNamedWindow</code> named window any rows that match in the value of the 'id' property either of the arriving events.
				</p>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_delete_namedwinsilent"></a>6.7.2. Silent Delete With Named Windows</h3>
						</div>
					</div>
				</div>
				<p>
					When using <code class="literal">on-delete</code> with named windows you may specify <code class="literal">@hint('silent_delete')</code> to suppress output of the deleted events to listeners of the statement that declares the named window.
					This is useful when listeners need to receive only expired events and not explicitly deleted events.
				</p>
				<p>
					The <code class="literal">@hint('silent_delete')</code> instruction for use with <code class="literal">on-delete</code> removes the dispatches for all listeners of the statement that declares the named window in respect to the deleted events,
					thereby listeners of the statement that declares the named window don't receive the deleted events.
				</p>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_delete_namedwin"></a>6.7.3. Notes on On-Delete With Named Windows</h3>
						</div>
					</div>
				</div>
				<p>
					Statements that reference the named window receive the deleted event as part of the remove stream.
				</p>
				<p>
					For correlated statements (as above) that correlate triggering events with rows held by a named window, the runtime internally creates efficient indexes to enable high performance deletion of rows.
				</p>
				<p>
					The <code class="literal">iterator</code> of the <code class="literal">EPStatement</code> object representing the <code class="literal">on delete</code> clause does not return any rows.
				</p>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_delete_table"></a>6.7.4. Notes on On-Update With Tables</h3>
						</div>
					</div>
				</div>
				<p>
					For correlated statements that correlate triggering events with rows held by a table, the runtime utilizes either primary key columns or
					secondary explicitly-created indexes to enable high performance querying of rows, based on an analysis of the <code class="literal">where</code> clause.
				</p>
				<p>
					The <code class="literal">iterator</code> of the <code class="literal">EPStatement</code> object representing the <code class="literal">on delete</code> clause does not return any rows.
				</p>
			</div>
		</div>
		<div class="sect1" lang="en-US">
			<div class="titlepage">
				<div>
					<div>
						<h2 class="title"><a id="named_upsert"></a>6.8. Triggered Upsert Using the <code class="literal">On-Merge</code> Clause</h2>
					</div>
				</div>
			</div><a id="d0e18575" class="indexterm"></a><a id="d0e18578" class="indexterm"></a><a id="d0e18581" class="indexterm"></a><a id="d0e18586" class="indexterm"></a><a id="d0e18591" class="indexterm"></a><a id="d0e18596" class="indexterm"></a>
			<p>
				The <code class="literal">on merge</code> clause is similar to the SQL <code class="literal">merge</code> clause. It provides what is known as an "Upsert" operation: Update existing rows or if no existing row(s) are found then insert a new row, all in an atomic operation provided by a single statement.
			</p>
			<p>
				The syntax for the <code class="literal">on merge</code> clause has two forms.
			</p>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_upsert_insertunmatched"></a>6.8.1. <code class="literal">On-Merge</code> Insert</h3>
						</div>
					</div>
				</div>
				<p>
					Use <code class="literal">on merge</code> with <code class="literal">insert</code> to inserts one row. This syntax is:
				</p>
				<pre class="synopsis">on <span class="emphasis"><em>event_type</em></span>[<span class="emphasis"><em>(filter_criteria)</em></span>] [as <span class="emphasis"><em>stream_name</em></span>]
merge [into] <span class="emphasis"><em>window_or_table_name</em></span> [as <span class="emphasis"><em>stream_name</em></span>]
insert [ (<span class="emphasis"><em>property_name</em></span> [, property_name] [,...]) ] select <span class="emphasis"><em>select_expression</em></span> [, <span class="emphasis"><em>select_expression</em></span>[,...]]</pre>
				<p>
					The <span class="emphasis"><em>event_type</em></span> is the name of the type of events that trigger the merge. It is optionally followed by <span class="emphasis"><em>filter_criteria</em></span> which are filter expressions to apply to arriving events. The optional <code class="literal">as</code> keyword can be used to assign a name for use in the <code class="literal">where</code> clause. Patterns and named windows can also be specified in the <code class="literal">on</code> clause as described in prior sections.
				</p>
				<p>
					The <span class="emphasis"><em>window_or_table_name</em></span> is the name of the named window to insert a row into.
					The <code class="literal">as</code> keyword is also available to assign a name to the named window or table.
				</p>
				<p>
					It follows the <code class="literal">insert</code> keyword and optionally the <code class="literal">into</code> keyword. Optionally you can provide a list of property names in parenthesis.
				</p>
				<p>
					It follows the required <code class="literal">select</code> keyword and one or more select-clause expressions. The wildcard (<code class="literal">*</code>) is available in the select-clause as well.
				</p>
				<p>
					On-merge can be used with tables and named windows. This examples uses the <code class="literal">SecuritySummaryTable</code> table that was defined earlier.
				</p>
				<p>
					This example statement inserts a row into the <code class="literal">SecuritySummaryTable</code> table when a <code class="literal">SecuritySummary</code> arrives:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on SecuritySummary merge SecuritySummaryTable insert select ipAddress, userId</pre>
				<p>
					The following EPL is equivalent to the statement above and uses the upsert syntax instead, described next.
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on SecuritySummary merge SecuritySummaryTable where 1=2 when not matched then insert select ipAddress, userId</pre>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_upsert_upsert"></a>6.8.2. <code class="literal">On-Merge</code> Update, Delete, Select (Upsert)</h3>
						</div>
					</div>
				</div>
				<p>
					The syntax for <code class="literal">on merge</code> for update-insert (upsert) is as follows:
				</p>
				<pre class="synopsis">on <span class="emphasis"><em>event_type</em></span>[<span class="emphasis"><em>(filter_criteria)</em></span>] [as <span class="emphasis"><em>stream_name</em></span>]
merge [into] <span class="emphasis"><em>window_or_table_name</em></span> [as <span class="emphasis"><em>stream_name</em></span>]
[where <span class="emphasis"><em>criteria_expression</em></span>]
  when [not] matched [and <span class="emphasis"><em>search_condition</em></span>]
    then [
      insert [into <span class="emphasis"><em>streamname</em></span>]
          [ (<span class="emphasis"><em>property_name</em></span> [, property_name] [,...]) ] 
          select <span class="emphasis"><em>select_expression</em></span> [, <span class="emphasis"><em>select_expression</em></span>[,...]]
          [where <span class="emphasis"><em>filter_expression</em></span>]
      |
      update set <span class="emphasis"><em>mutation_expression</em></span> [, <span class="emphasis"><em>mutation_expression</em></span> [,...]]
          [where <span class="emphasis"><em>filter_expression</em></span>]
      |
      delete
          [where <span class="emphasis"><em>filter_expression</em></span>]
    ]
    [then [insert|update|delete]] [,then ...]
  [when ...  then ... [...]] </pre>
				<p>
					The <span class="emphasis"><em>event_type</em></span> is the name of the type of events that trigger the merge. It is optionally followed by <span class="emphasis"><em>filter_criteria</em></span> which are filter expressions to apply to arriving events. The optional <code class="literal">as</code> keyword can be used to assign a name for use in the <code class="literal">where</code> clause. Patterns and named windows can also be specified in the <code class="literal">on</code> clause as described in prior sections.
				</p>
				<p>
					The <span class="emphasis"><em>window_or_table_name</em></span> is the name of the named window or table to insert, update or delete rows.
					The <code class="literal">as</code> keyword is also available to assign a name to the named window or table.
				</p>
				<p>
					The optional <code class="literal">where</code> clause contains a <span class="emphasis"><em>criteria_expression</em></span> that correlates the arriving (triggering) event to the rows to be considered of the table or named window.
					We recommend specifying a criteria expression that is as specific as possible.
				</p>
				<p>
					Following the <code class="literal">where</code> clause is one or more <code class="literal">when matched</code> or <code class="literal">when not matched</code> clauses in any order. Each may have an additional search condition associated.
				</p>
				<p>
					After each <code class="literal">when [not] matched</code> follow one or more <code class="literal">then</code> clauses that each contains the action to take: Either an <code class="literal">insert</code>, <code class="literal">update</code> or <code class="literal">delete</code> keyword.
				</p>
				<p>
					After <code class="literal">when not matched</code> only <code class="literal">insert</code> action(s) are available. After <code class="literal">when matched</code> any <code class="literal">insert</code>, <code class="literal">update</code> and <code class="literal">delete</code> action(s) are available.
				</p>
				<p>
					After <code class="literal">insert</code> follows, optionally, the <code class="literal">into</code> keyword followed by the stream name or named window to insert-into. If no <code class="literal">into</code> and stream name is specified, the insert applies to the current table or named window. It follows an optional list of columns inserted. It follows the required <code class="literal">select</code> keyword and one or more select-clause expressions. The wildcard (<code class="literal">*</code>) is available in the select-clause as well. It follows an optional where-clause that may return Boolean false to indicate that the action should not be applied.
				</p>
				<p>
					After <code class="literal">update</code> follows the <code class="literal">set</code> keyword and one or more mutation expressions. For mutation expressions please see <a class="xref" href="nwtable.html#named_update" title="6.6. Updating Data: The On Update Clause">Section 6.6, “Updating Data: The On Update Clause”</a>.
					It follows an optional where-clause that may return Boolean false to indicate that the action should not be applied.
				</p>
				<p>
					After <code class="literal">delete</code> follows an optional where-clause that may return Boolean false to indicate that the action should not be applied.
				</p>
				<p>
					When according to the where-clause <span class="emphasis"><em>criteria_expression</em></span> the runtime finds no rows in the named window or table that match the condition, the runtime evaluates each <span class="emphasis"><em>when not matched</em></span> clause. If the optional search condition returns true or no search condition was provided then the runtime performs all of the actions listed after each <code class="literal">then</code>.
				</p>
				<p>
					When according to the where-clause <span class="emphasis"><em>criteria_expression</em></span> the runtime finds one or more rows in the named window or table that match the condition, the runtime evaluates each <span class="emphasis"><em>when matched</em></span> clause. If the optional search condition returns true or no search condition was provided the runtime performs all of the actions listed after each <code class="literal">then</code>.
				</p>
				<p>
					The runtime executes <code class="literal">when matched</code> and <code class="literal">when not matched</code> in the order specified. If the optional search condition returns true or no search condition was specified then the runtime takes the associated action (or multiple actions for multiple <code class="literal">then</code> keywords). When the block of actions completed the runtime proceeds to the next matching row, if any. After completing all matching rows the runtime continues to the next triggering event if any.
				</p>
				<p>
					On-merge can be used with tables and named windows. The examples herein declare a <code class="literal">ProductWindow</code> named window and also use the <code class="literal">SecuritySummaryTable</code> table to provide examples for each.
				</p>
				<p>
					This example statement updates the <code class="literal">SecuritySummaryTable</code> table when a <code class="literal">ResetEvent</code> arrives setting the <code class="literal">active</code> column's value to false:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on ResetEvent merge SecuritySummaryTable
when matched and active = true then update set active = false</pre>
				<p>
					A longer example utilizing a named window follows. You start by declaring a schema that provides a product id and that holds a total price:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create schema ProductTotalRec as (productId string, totalPrice double)</pre>
				<p>
					We create a named window that holds a row for each unique product:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create window ProductWindow#unique(productId) as ProductTotalRec</pre>
				<p>
					The events for this example are order events that hold an order id, product id, price, quantity and deleted-flag declared by the next schema:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create schema OrderEvent as (orderId string, productId string, price double, 
    quantity int, deletedFlag boolean)</pre>
				<p>
					The following statement utilizes <code class="literal">on-merge</code> to total up the price for each product based on arriving order events:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on OrderEvent oe
  merge ProductWindow pw
  where pw.productId = oe.productId
  when matched
    then update set totalPrice = totalPrice + oe.price
  when not matched 
    then insert select productId, price as totalPrice</pre>
				<p>
					In the above example, when an order event arrives, the runtime looks up in the product named window the matching row or rows for the same product id as the arriving event. In this example the runtime always finds no row or one row as the product named window is declared with a unique data window based on product id.
					If the runtime finds a row in the named window, it performs the update action adding up the price as defined under <code class="literal">when matched</code>. If the runtime does not find a row in the named window it performs the insert action as defined under <code class="literal">when not matched</code>, inserting a new row.
				</p>
				<p>
					The <code class="literal">insert</code> keyword may be followed by a list of columns as shown in this EPL snippet:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// equivalent to the insert shown in the last 2 lines in above EPL
...when not matched 
    then insert(productId, totalPrice) select productId, price</pre>
				<p>
					The second example demonstrates the use of a select-clause with wildcard, a search condition and the <code class="literal">delete</code> keyword. It creates a named window that holds order events and employs on-merge to insert order events for which no corresponding order id was found, update quantity to the quantity provided by the last arriving event and delete order events that are marked as deleted:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create window OrderWindow#keepall as OrderEvent</pre>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on OrderEvent oe
  merge OrderWindow pw
  where pw.orderId = oe.orderId
  when not matched 
    then insert select *
  when matched and oe.deletedFlag=true
    then delete
  when matched
    then update set pw.quantity = oe.quantity, pw.price = oe.price</pre>
				<p>
					In the above example the <code class="literal">oe.deletedFlag=true</code> search condition instructs the runtime to take the delete action only if the deleted-flag is set.
				</p>
				<p>
					You may specify multiple actions by providing multiple <code class="literal">then</code> keywords each followed by an action. Each of the <code class="literal">insert</code>, <code class="literal">update</code> and <code class="literal">delete</code> actions can itself have a where-clause as well.
					If a where-clause exists for an action, the runtime evaluates the where-clause and applies the action only if the where-clause returns Boolean true.
				</p>
				<p>
					This example specifies two update actions and uses the where-clause to trigger different update behavior depending on whether the order event price is less than zero.
					This example assumes that the host application defined a <code class="literal">clearorder</code> user-defined function, to demonstrate calling a user-defined function as part of the update mutation expressions:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on OrderEvent oe
  merge OrderWindow pw
  where pw.orderId = oe.orderId
  when matched
    then update set clearorder(pw) where oe.price &lt; 0
    then update set pw.quantity = oe.quantity, pw.price = oe.price where oe.price &gt;= 0</pre>
				<p>
					To insert events into another stream and not the named window, use <code class="literal">insert</code> <code class="literal">into</code> <span class="emphasis"><em>streamname</em></span>.
				</p>
				<p>
					In the next example each matched-clause contains two actions, one action to insert a log event and a second action to insert, delete or update:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">on OrderEvent oe
  merge OrderWindow pw
  where pw.orderId = oe.orderId
  when not matched 
    then insert into LogEvent select 'this is an insert' as name
    then insert select *
  when matched and oe.deletedFlag=true
    then insert into LogEvent select 'this is a delete' as name
    then delete
  when matched
    then insert into LogEvent select 'this is a update' as name
    then update set pw.quantity = oe.quantity, pw.price = oe.price</pre>
				<p>
					While the runtime evaluates and executes all actions listed under the same matched-clause in order, you may not rely on updated field values of an earlier action to trigger the where-clause of a later action. Similarly you should avoid simultaneous update and delete actions for the same match: the runtime does not guarantee whether the update or the delete take final affect.
				</p>
				<p>
					Your application can subscribe a listener to <code class="literal">on merge</code> statements to determine inserted, updated and removed events. Statements post any events that are inserted to, updated or deleted from a named window to all listeners attached to the statement as new data and removed data.
				</p>
				<p>
					The following limitations apply to on-merge statements:
				</p>
				<div xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="orderedlist">
					<ol>
						<li>
							<p>
								Aggregation functions and the <code class="literal">prev</code> and <code class="literal">prior</code> operators are not available in conditions and the <code class="literal">select</code>-clause.
							</p>
						</li>
					</ol>
				</div>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_merge_namedwin"></a>6.8.3. Notes on On-Merge With Named Windows</h3>
						</div>
					</div>
				</div>
				<p>
					Statements that reference the named window receive an insert and remove stream represening the insertions, changes and deletions to named window rows.
				</p>
				<p>
					For correlated statements (as above) that correlate triggering events with rows held by a named window, the runtime internally creates efficient indexes to enable high performance update and removal of events especially from named windows that hold large numbers of events.
				</p>
				<p>
					The <code class="literal">iterator</code> of the <code class="literal">EPStatement</code> object representing the <code class="literal">on merge</code> clause does not return any rows.
				</p>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_merge_table"></a>6.8.4. Notes on On-Merge With Tables</h3>
						</div>
					</div>
				</div>
				<p>
					On-Merge may not update primary key columns.
				</p>
				<p>
					For correlated statements that correlate triggering events with rows held by a table, the runtime utilizes either primary key columns or
					secondary explicitly-created indexes to enable high performance querying of rows, based on an analysis of the <code class="literal">where</code> clause.
				</p>
				<p>
					The <code class="literal">iterator</code> of the <code class="literal">EPStatement</code> object representing the <code class="literal">on merge</code> clause does not return any rows.
				</p>
			</div>
		</div>
		<div class="sect1" lang="en-US">
			<div class="titlepage">
				<div>
					<div>
						<h2 class="title"><a id="named_explicit_index"></a>6.9. Explicitly Indexing Named Windows and Tables</h2>
					</div>
				</div>
			</div><a id="d0e19088" class="indexterm"></a><a id="d0e19093" class="indexterm"></a><a id="d0e19098" class="indexterm"></a><a id="d0e19103" class="indexterm"></a><a id="d0e19108" class="indexterm"></a>
			<p>
				You may explicitly create an index on a table or a named window.
				The runtime considers explicitly-created as well as implicitly-allocated indexes (named windows only) in query planning and execution of the following
				types of usages of tables and named windows:
			</p>
			<div xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="orderedlist">
				<ol>
					<li>
						<p>Fire-and-forget (on-demand, non-continuous) queries as described in <a class="xref" href="apiruntime.html#apiruntime-fireandforget" title="16.7. Execute Fire-and-Forget Queries Using EPFireAndForgetService">Section 16.7, “Execute Fire-and-Forget Queries Using EPFireAndForgetService”</a>.</p>
					</li>
					<li>
						<p>
							<code class="literal">On-select</code>, <code class="literal">on-merge</code>, <code class="literal">on-update</code>, <code class="literal">on-delete</code> and <code class="literal">on-insert</code>.
						</p>
					</li>
					<li>
						<p>
							Subqueries against tables and named windows.
						</p>
					</li>
					<li>
						<p>
							For joins (including outer joins) with named windows the runtime considers the filter criteria listed in parenthesis using the syntax </p>
						<pre class="synopsis"><span class="emphasis"><em>name_window_name</em></span>(<span class="emphasis"><em>filter_criteria</em></span>)</pre>
						<p> for index access.
						</p>
					</li>
					<li>
						<p>
							For joins with tables the runtime considers the primary key columns (if any) as well as any table indexes.
						</p>
					</li>
				</ol>
			</div>
			<p>
				The syntax to create an explicit index on a named window or table is:
			</p>
			<pre class="synopsis">create [unique] index <span class="emphasis"><em>index_name</em></span> on <span class="emphasis"><em>window_or_table_name</em></span> (
  <span class="emphasis"><em>column_expression</em></span> [hash|btree|<span class="emphasis"><em>index_type_expression</em></span>] 
  [, <span class="emphasis"><em>column_expression</em></span>] [hash|btree|<span class="emphasis"><em>index_type_expression</em></span>] 
  [,...]
)</pre>
			<p>
				The optional <span class="emphasis"><em>unique</em></span> keyboard indicates that the column expressions uniquely identify rows. If <span class="emphasis"><em>unique</em></span> is not specified
				the index allows duplicate rows.
			</p>
			<p>
				The <span class="emphasis"><em>index_name</em></span> is the name assigned to the index. The name uniquely identifies the index and is used in compiler query plan logging.
			</p>
			<p>
				The <span class="emphasis"><em>window_or_table_name</em></span> is the name of an existing table or named window. If the named window or table has rows already, the runtime builds an index for the rows.
			</p>
			<p>
				After the table name or named window name follows a list of pairs of <span class="emphasis"><em>column_expression</em></span> column expression and index type.
			</p>
			<p>
				A column expression is the expression that is subject to index building.
				Typically a column expression is an event property or column name.
				For special application-provided or spatial indexes other column expressions are allowed and such indexes may allow multiple columns to be combined.
				Further detail on key expressions can be found at <a class="xref" href="epl_clauses.html#epl-syntax-compositekey" title="5.2.13. Composite Keys and Array Values as Keys">Section 5.2.13, “Composite Keys and Array Values as Keys”</a>.
			</p>
			<p>
				Following each column expression you may specify the index type by providing the optional <code class="literal">hash</code> or <code class="literal">btree</code> keywords or an <span class="emphasis"><em>index_type_expression</em></span>.
				For special application-provided or spatial indexes please use the <span class="emphasis"><em>index_type_expression</em></span>.
			</p>
			<p>
				If you specify no keyword or the <code class="literal">hash</code> keyword for a property, the index will be a hash-based (unsorted) index in respect to that property. If you specify the <code class="literal">btree</code> keyword, the index will be a binary-tree-based sorted index in respect to that property.
				You may combine <code class="literal">hash</code> and <code class="literal">btree</code> properties for the same index.
				Specify <code class="literal">btree</code> for a property if you expect to perform numerical or string comparison using relational operators (&lt;, &gt;, &gt;=, &lt;=), the <code class="literal">between</code> or the <code class="literal">in</code> keyword for ranges and inverted ranges. Use <code class="literal">hash</code> (the default) instead of <code class="literal">btree</code> if you expect to perform exact comparison using <code class="literal">=</code>.
			</p>
			<p>
				For <code class="literal">hash</code> and <code class="literal">btree</code> index types the column expression must be an event property or column name. Expressions such as <code class="literal">col+1</code> are not currently supported for
				<code class="literal">hash</code> and <code class="literal">btree</code> index types but are supported for other index types.
			</p>
			<p>
				The <code class="literal">create table</code> syntax is the same for tables and named windows. The examples herein create a new <code class="literal">UserProfileWindow</code> named window and also use the <code class="literal">SecuritySummaryTable</code> table.
			</p>
			<p>
				This sample EPL creates an non-unique index on the <code class="literal">active</code> column of table <code class="literal">SecuritySummaryTable</code>:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create index MyIndex on SecuritySummaryTable(active)</pre>
			<p>
				We list a few example statements next that create a named window and create a single index:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// create a named window
create window UserProfileWindow#time(1 hour) select * from UserProfile</pre>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// create a non-unique index (duplicates allowed) for the user id property only
create index UserProfileIndex on UserProfileWindow(userId)</pre>
			<p>
				Next, execute a fire-and-forget query as shown below; this sample uses the prepared version to demonstrate:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">String query = "select * from UserProfileWindow where userId='Joe'";
EPFireAndForgetPreparedQuery prepared = epRuntime.getFireAndForgetService().prepareQuery(query);
// query performance excellent in the face of large number of rows
EPFireAndForgetQueryResult result = prepared.execute();
// ...later ...
prepared.execute();	// execute a second time</pre>
			<p>
				A unique index is generally preferable over non-unique indexes. For named windows, if your data window declares a unique data window (<code class="literal">#unique</code>, <code class="literal">#firstunique</code>, including intersections and grouped unique data windows) it is not necessary to create a unique index unless index sharing is enabled, since the compiler and runtime considers the unique data window declaration in query planning.
			</p>
			<p>
				The runtime enforces uniqueness (e.g. unique constraint) for unique indexes. If your application inserts a duplicate row the runtime raises a runtime exception when processing the statement and discards the row. The default error handler logs such an exception and continues.
			</p>
			<p>
				For example, if the user id together with the profile id uniquely identifies an entry into the named window, your application can create a unique index as shown below:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// create a unique index on user id and profile id
create unique index UserProfileIndex on UserProfileWindow(userId, profileId)</pre>
			<p>
				By default, the runtime builds a hash code -based index useful for direct comparison via equals (=). Filter expressions that look for ranges or use <code class="literal">in, between </code> do not benefit from the hash-based index and should use the <code class="literal">btree</code> keyword. For direct comparison via equals (=) then compiler does not use <code class="literal">btree</code> indexes.
			</p>
			<p>
				The next example creates a composite index over two fields <code class="literal">symbol</code> and <code class="literal">buyPrice</code>:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// create a named window
create window TickEventWindow#time(1 hour) as (symbol string, buyPrice double)</pre>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// create a non-unique index 
create index idx1 on TickEventWindow(symbol hash, buyPrice btree)</pre>
			<p>
				A sample fire-and-forget query is shown below (this time the API calls are not shown):
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">// query performance excellent in the face of large number of rows
select * from TickEventWindow where symbol='GE' and buyPrice between 10 and 20</pre>
			<div xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="note">
				<h2>Note</h2>
				<p>A table that does not declare one or more primary key columns cannot have a secondary index, as the table holds a maximum of one row.</p>
			</div>
		</div>
		<div class="sect1" lang="en-US">
			<div class="titlepage">
				<div>
					<div>
						<h2 class="title"><a id="nwtable-faf"></a>6.10. Using Fire-and-Forget Queries With Named Windows and Tables</h2>
					</div>
				</div>
			</div>
			<p>
				Fire-and-Forget queries can be run against both tables and named windows. We use the term <span class="emphasis"><em>property</em></span> and <span class="emphasis"><em>column</em></span> interchangeably.
			</p>
			<p>
				For selecting from named windows and tables, please see the examples in <a class="xref" href="apiruntime.html#apiruntime-fireandforget" title="16.7. Execute Fire-and-Forget Queries Using EPFireAndForgetService">Section 16.7, “Execute Fire-and-Forget Queries Using EPFireAndForgetService”</a>.
			</p>
			<p>
				For data manipulation (insert, update, delete) queries, the fire-and-forget query API returns the inserted, updated or deleted rows when the query executes against a named window.
			</p>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_insertfaf"></a>6.10.1. Inserting Data</h3>
						</div>
					</div>
				</div><a id="d0e19362" class="indexterm"></a><a id="d0e19365" class="indexterm"></a><a id="d0e19370" class="indexterm"></a><a id="d0e19375" class="indexterm"></a>
				<p>
					Your application can insert rows into a table or named window using fire-and-forget (on-demand, non-continuous) queries as described in <a class="xref" href="apiruntime.html#apiruntime-fireandforget" title="16.7. Execute Fire-and-Forget Queries Using EPFireAndForgetService">Section 16.7, “Execute Fire-and-Forget Queries Using EPFireAndForgetService”</a>.
				</p>
				<p>
					The compiler allows the standard SQL syntax and <code class="literal">values</code> keyword and also supports using <code class="literal">select</code> to provide values.
					The <code class="literal">values</code> keyword allows inserting multiple rows in a single query.
				</p>
				<p>
					The syntax using the <code class="literal">values</code> keyword is:
				</p>
				<pre class="synopsis">insert into <span class="emphasis"><em>window_or_table_name</em></span> [(<span class="emphasis"><em>property_names</em></span>)]
values (<span class="emphasis"><em>value_expressions</em></span>) [, (<span class="emphasis"><em>value_expressions</em></span>) ...]</pre>
				<p>
					The syntax using <code class="literal">select</code> is as follows:
				</p>
				<pre class="synopsis">insert into <span class="emphasis"><em>window_or_table_name</em></span> [(<span class="emphasis"><em>property_names</em></span>)]
select <span class="emphasis"><em>value_expressions</em></span></pre>
				<p>
					The <span class="emphasis"><em>window_or_table_name</em></span> is the name of the table or named window to insert rows into.
				</p>
				<p>
					After the named window or table name you can optionally provide a comma-separated list of property names.
				</p>
				<p>
					When providing property names, the order of value expressions in the values list or select clause must match the order of property names specified. Column names provided in the select-clause, if specified, are ignored.
				</p>
				<p>
					When not providing property names and when specifying the <code class="literal">values</code> keyword, the order of values must match the order of properties
					declared for the named window or table.
					When not providing property names and when specifying the select-clause, expressions must name the properties to be inserted into
					by assigning a column name using the <code class="literal">as</code> keyword.
				</p>
				<p>
					The example code snippet inserts a new order row into the <code class="literal">OrdersWindow</code> named window:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">String query = 
  "insert into OrdersWindow(orderId, symbol, price) values ('001', 'GE', 100)";
runtime.getFireAndForgetService().executeQuery(query);</pre>
				<p>
					After the <code class="literal">values</code> keyword there can be multiple comma-separated lists of values for insertion. A maximum of up to 1000 rows can be inserted per query.
				</p>
				<p>
					The sample query below inserts two order rows:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">insert into OrdersWindow(orderId, symbol, price) values ('001', 'GE', 100), ('002', 'IBM', 75)</pre>
				<p>
					Instead of the <code class="literal">values</code> keyword you may specify a select-clause as this example shows:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">String query = 
  "insert into OrdersWindow(orderId, symbol, price) select '001', 'GE', 100";
runtime.getFireAndForgetService().executeQuery(query);</pre>
				<p>
					The following EPL inserts the same values as above but specifies property names as part of the select-clause expressions:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">insert into OrdersWindow
select '001' as orderId, 'GE' as symbol, 100 as price</pre>
				<p>
					The next EPL inserts the same values as above and does not specify property names thereby
					populating the first 3 properties of the type of the named window:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">insert into OrdersWindow values ('001', 'GE', 100)</pre>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_update_faf"></a>6.10.2. Updating Data</h3>
						</div>
					</div>
				</div><a id="d0e19482" class="indexterm"></a><a id="d0e19485" class="indexterm"></a><a id="d0e19490" class="indexterm"></a><a id="d0e19495" class="indexterm"></a>
				<p>
					Your application can update table and named window rows using fire-and-forget (on-demand, non-continuous) queries as described in <a class="xref" href="apiruntime.html#apiruntime-fireandforget" title="16.7. Execute Fire-and-Forget Queries Using EPFireAndForgetService">Section 16.7, “Execute Fire-and-Forget Queries Using EPFireAndForgetService”</a>.
				</p>
				<p>
					The syntax for the <code class="literal">update</code> clause is as follows:
				</p>
				<pre class="synopsis">update <span class="emphasis"><em>window_or_table_name</em></span> [as <span class="emphasis"><em>stream_name</em></span>]
set <span class="emphasis"><em>mutation_expression</em></span> [, <span class="emphasis"><em>mutation_expression</em></span> [,...]]
[where <span class="emphasis"><em>criteria_expression</em></span>]</pre>
				<p>
					The <span class="emphasis"><em>window_or_table_name</em></span> is the name of the table or named window to remove rows from.
					The <code class="literal">as</code> keyword is also available to assign a name to the table or named window.
				</p>
				<p>
					After the <code class="literal">set</code> keyword follows a comma-separated list of mutation expressions. For fire-and-forget queries the following restriction applies: Aggregation functions and the <code class="literal">prev</code> or <code class="literal">prior</code> function may not be used in expressions. Mutation expressions are detailed in <a class="xref" href="nwtable.html#named_update" title="6.6. Updating Data: The On Update Clause">Section 6.6, “Updating Data: The On Update Clause”</a>.
				</p>
				<p>
					The optional <code class="literal">where</code> clause contains a <span class="emphasis"><em>criteria_expression</em></span> that identifies rows to be updated.
				</p>
				<p>
					The example code snippet updates those rows of the named window that have a negative value for volume:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">String query = "update OrdersNamedWindow set volume = 0 where volumne = 0";
runtime.getFireAndForgetService().executeQuery(query);</pre>
				<p>
					To instruct the runtime to use the initial property value before update, prefix the property name with the literal <code class="literal">initial</code>.
				</p>
			</div>
			<div class="sect2" lang="en-US">
				<div class="titlepage">
					<div>
						<div>
							<h3 class="title"><a id="named_delete_faf"></a>6.10.3. Deleting Data</h3>
						</div>
					</div>
				</div><a id="d0e19569" class="indexterm"></a><a id="d0e19572" class="indexterm"></a><a id="d0e19577" class="indexterm"></a><a id="d0e19582" class="indexterm"></a>
				<p>
					Your application can delete rows from a named window or table using fire-and-forget (on-demand, non-continuous) queries as described in <a class="xref" href="apiruntime.html#apiruntime-fireandforget" title="16.7. Execute Fire-and-Forget Queries Using EPFireAndForgetService">Section 16.7, “Execute Fire-and-Forget Queries Using EPFireAndForgetService”</a>.
				</p>
				<p>
					The syntax for the <code class="literal">delete</code> clause is as follows:
				</p>
				<pre class="synopsis">delete from <span class="emphasis"><em>window_or_table_name</em></span> [as <span class="emphasis"><em>stream_name</em></span>]
	[where <span class="emphasis"><em>criteria_expression</em></span>]</pre>
				<p>
					The <span class="emphasis"><em>window_or_table_name</em></span> is the name of the named window or table to delete rows from.
					The <code class="literal">as</code> keyword is also available to assign a name to the named window or table.
				</p>
				<p>
					The optional <code class="literal">where</code> clause contains a <span class="emphasis"><em>criteria_expression</em></span> that identifies rows to be removed from the named window or table.
				</p>
				<p>
					The example code snippet deletes from a named window all rows that have a negative value for volume:
				</p>
				<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">String query = "delete from OrdersNamedWindow where volume &lt;= 0";
runtime.getFireAndForgetService().executeQuery(query);</pre>
			</div>
		</div>
		<div class="sect1" lang="en-US">
			<div class="titlepage">
				<div>
					<div>
						<h2 class="title"><a id="named-eventasproperty"></a>6.11. Events as Property</h2>
					</div>
				</div>
			</div>
			<p>
				Columns in a named window and table may also hold an event or multiple events. More information on the <code class="literal">insert into</code> clause providing event columns is in <a class="xref" href="epl_clauses.html#insert-into-eventasproperty" title="5.10.5. Event as a Property">Section 5.10.5, “Event as a Property”</a>.
			</p>
			<p>
				A sample declaration for a named window and a table is:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create schema InnerData (value string)</pre>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create table ContainerTable (innerdata InnerData)</pre>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create window ContainerWindow#time(30) as (innerdataArray InnerData[]) // array of events</pre>
			<p>
				The second sample creates a named window that specifies two columns: A column that holds an OrderEvent, and a column by name <code class="literal">priceTotal</code>. A matching <code class="literal">insert into</code> statement is also part of the sample:
			</p>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">create window OrdersWindow#time(30) as select this, price as priceTotal from OrderEvent</pre>
			<pre xmlns="" xmlns:d="http://docbook.org/ns/docbook" xmlns:rf="java:org.jboss.highlight.XhtmlRendererFactory" class="">insert into OrdersWindow select order, price * unit as priceTotal  
from ServiceOrderEvent as order</pre>
			<p>
				Note that the <code class="literal">this</code> proprerty must exist on the event and must return the event class itself (JavaBean events only). The property type of the additional <code class="literal">priceTotal</code> column is the property type of the existing <code class="literal">price</code> property.
			</p>
		</div>
	</div>
	<ul xmlns:d="http://docbook.org/ns/docbook" class="docnav">
		<li class="previous"><a accesskey="p" href="epl_clauses.html"><strong>Prev</strong>Chapter 5. EPL Reference: Clauses</a></li>
		<li class="up"><a accesskey="u" href="#"><strong>Top of page</strong></a></li>
		<li class="home"><a accesskey="h" href="index.html"><strong>Front page</strong></a></li>
		<li class="next"><a accesskey="n" href="event_patterns.html"><strong>Next</strong>Chapter 7. EPL Reference: Patterns</a></li>
	</ul>
</body>

</html>